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.

The syntax is very simple:

BEGIN
    [CODE]
END

Let’s look at a simple example with a code snippet that generates a loop:

BEGIN…END example:

DECLARE @iter INT = 1;
WHILE @iter <= 10
BEGIN
    SELECT @iter
    SET @iter = @iter + 1
END

The @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 name , lastName , product and price from the Customers and Orders tables for the orderId = 3.