Begin SQL

Introduction to Begin SQL

Begin SQL is the keyword that is used to mark up and specify the beginning of the transaction or stored procedure or functions or simply the collection of multiple statements inside the logical block whose body starts from the specification of the BEGIN keyword and stops with the use of END keyword. We can write the sequence of the statement that we wish to execute inside the BEGIN and END keywords in SQL. Multiple BEGIN END statements can be written inside the same method or stored procedure, etc. We can even write nested BEGIN END statements in SQL where the logical block of statements defined between BEGIN and END keywords can be executed on a conditional basis or inside the loops and other functions according to the use case and requirement.

In this article, we will learn about the syntax of using the BEGIN keyword in SQL and the END keyword that helps create a logical block of SQL statements with the help of syntax and few examples.

Syntax

The following is the syntax of using the BEGIN keyword in SQL, which is accompanied with END for termination of the block of statements –

BEGIN
// Statements to be executed in the batch
END

The use of BEGIN is mostly done in Transact-SQL, where multiple statements are executed completely in a transactional manner to maintain the consistency of the database operations and ACID properties of the database. We can mention the statements that we want to execute in a transactional manner inside the BEGIN and END keywords, as shown in the above syntax. The statements inside the BEGIN and END are also known as the batch that is executed together. We can mention the set of statements of SQL inside the BEGIN and END keywords.

Let us consider a few of the examples that will make the implementation of BEGIN in SQL clear to you.

Examples of Begin SQL

We will consider one existing table named students that are present inside the database named educba on my SQL server. The structure and contents of the table students can be seen from the output of the following query statement –

SELECT * FROM students;

The output of the execution of the above query statement is as follows –

The students’ table contains 14 records in it. Now, we want to execute three statements in the transact SQL that includes the addition of two records named Karna and Yudhishthira to the students’ table and, upon addition, retrieval of the records of the students’ table. We will place our SQL query statements of INSERT and SELECT queries inside the BEGIN and END keyword as shown in the below code –

BEGIN
INSERT INTO `students` (`student_id`, `class_id`, `name`, `roll`, `technology`, `percentage`) VALUES('15','3','Karna','Manager','Maven','96%');
INSERT INTO `students` (`student_id`, `class_id`, `name`, `roll`, `technology`, `percentage`) VALUES('16','2','Yudhistir','Administrator','MySQL','92%');
SELECT * FROM students;
END

The output of the execution of the above query statements and block is as follows –

We can observe that the two additional records have been inserted into the students’ table, and now, the students’ table contains 16 records in total that are retrieved from the third SELECT query that we mentioned in our logical block between BEGIN and END keywords.

Let us now consider one more example in which we want to get the records of the student’s names and id that have a percentage greater than 92, and if no records are retrieved, then it should give the output as ‘No students found that have achieved percentage greater than 92’. For this, we will first select the records from the student’s table that will have a percentage greater than 92 using the select query, and then if the row count of the retrieved query is zero, then we will select the string literal value that will contain the sentence as required when no such record is found.

We will use the IF statement to check the row count condition and will place all these statements inside the BEGIN and END to make them a single logical block that will consist of our statements as shown below –

BEGIN
SELECT
student_id,
NAME
FROM
educba.students
WHERE
percentage > 92;
IF @@ROWCOUNT = 0
SELECT 'No students found that have achieved percentage greater than 92';
END

The output of the execution of the above query statements and block is as follows –

Nested BEGIN and END keyword usage

We can even make the use of nested BEGIN END blocks inside each other and create different logical blocks of statements to be executed. The BEGIN and END togetherly work as { and } in C, JAVA, or any other language. We can execute these logical blocks based on some conditions and also repetitively inside various looping statements of SQL.

Let us consider one simple example of nested BEGIN END blocks in SQL that will retrieve the name of the students that are ordered in the descending manner of their percentages and then get the value of the first record from the query that retrieves the names of students and stores the name of the student with the highest percentage inside the variable named @name. Further, we want to check if the row count of the result set of the query statement is not equal to zero, then print the value of the name in the string ‘The highest scoring student is the name of student else execute another block of BEGIN END that will get the string value ‘No student found’. We will use the following code for that –

BEGIN
DECLARE @name VARCHAR(100);
SELECT
@name = NAME
FROM
educba.students
ORDER BY
percentage DESC;
IF @@ROWCOUNT <> 0
BEGIN
SELECT 'The most highest scoring student is ' + @name;
END
ELSE
BEGIN
SELECT 'No student found';
END;
END

The output of the execution of the above query statements and block is followed, retrieving the name of student Karna that has the highest percentage that is 96% –

Conclusion

We can make the use of the BEGIN keyword in SQL to mark the starting point of the logical block of statements of transact SQL that we need to specify and use the END keyword to specify the closing point of a logical block in SQL.

Recommended Articles

This is a guide to Begin SQL. Here we discuss the syntax of using the BEGIN keyword in SQL and the END keyword that helps create a logical block of SQL statements. You can also look at the following article to learn more –

1. MySQL Sync
2. SQL Virtual Table
3. SQL Pattern Matching
4. SQL Except Select