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.
ADVERTISEMENT Popular Course in this category SQL - Specialization | 51 Course Series | 6 Mock TestsHadoop, Data Science, Statistics & others
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 ENDThe 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.
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; ENDThe 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'; ENDThe output of the execution of the above query statements and block is as follows –
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; ENDThe 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% –
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.
We hope that this EDUCBA information on “Begin SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
