BEGIN END statement in Transact SQL
BEGIN END is a flow control structure used to hold multiple statements grouped together in a given code block. In short, they allow a series of instructions to be executed together.
BEGIN END can be nested within a block of code.
It is recommended to get used to the use of BEGIN END, even in single statements when working with flow control structures.
The syntax is very simple:
BEGIN [CODE] END
Let’s look at a simple example with a code snippet that generates a loop:
DECLARE @iter INT = 1; WHILE @iter <= 10 BEGIN SELECT @iter SET @iter = @iter + 1 END
@iter variable will increase by one for each iteration of the loop until the variable reaches the value
@iter= 10, wich is the last valid value in the
WHILE. This means that the execution result will print ten times the
@iter variable on the screen, starting with the initial value 1 and ending with the value 10.
To better understand how the BEGIN END statement works, we will use the same code as before but deleting the flow control structures.
DECLARE @iter INT = 1; WHILE @iter <= 10 SELECT @iter as iter SET @iter = @iter + 1
We will see that by not grouping the statements, our code is stuck in infinite loop showing 1 as an execution result on each iteration.
Next , we will see an example of nested BEGIN END modifying the previous code:
DECLARE @iter INT = 1; WHILE @iter <= 10 BEGIN SELECT @iter as iter IF @iter = 3 BEGIN SELECT ('INFO REGISTRATION:') AS INFO; SELECT name, lastName, product, price FROM Customers INNER JOIN Orders ON Customers.customerId = Orders.customerId WHERE Customers.customerId = @iter END SET @iter = @iter + 1 END
The exeution will print the value of the
@iter variable from 1 to 10 as in the previous example,but when it reaches the 3 value, the code will fulfill the IF condition executing the instrunctions that BEGIN END contains. This will print a information message and will return the values of the fields
price from the
Orders tables for the
orderId = 3.