Loops in SQL Server
In SQL Server, a loop is the technique where a set of SQL statements are executed repeatedly until a condition is met.
SQL Server supports the WHILE loop. The execution of the statements can be controlled from within the WHLE block using BREAK and CONTINUE keywords.
WHILE <condition> SQL Statement | statement_block | BREAK | CONTINUE
Boolean_expression : A boolean expression that returns TRUE or FALSE.
sql_statement | statement_block : A single or a group of SQL statements (statement block). Statement block should be enclosed with BEGIN and END keywords.
BREAK: Causes the flow to exit from the innermost WHILE loop. Statements after the END keyword are executed after the BREAK.
CONTINUE: Causes the WHILE loop to restart. It ignores all statements after the CONTINUE keyword.
The following example uses the WHILE loop to print numbers.
DECLARE @i INT = 10; WHILE @i <= 30 BEGIN PRINT (@i); SET @i = @i + 10; END;
The following displays the result in SSMS.
In the above example, an integer variable
@i is set to 10. The condition of the WHILE loop
@i <= 30 indicates that it will execute the block until
@i is 30. The block inside the WHILE loop is wrapped within the BEGIN and END keywords. The variable
@i is printed and then incremented with 10.
The following example uses the BREAK keyword to exit from the loop.
DECLARE @i INT = 10; WHILE @i <= 30 BEGIN PRINT (@i); SET @i = @i + 10; IF @i = 30 BREAK; END;
In the above example, the statement prints the value of the variable
@i until the condition
@i = 30 is reached where the BREAK keyword takes effect, and the loop exits.
In the following example, when the condition of the WHILE loop (
@i <= 30) is reached, the CONTINUE keyword will make the loop go to infinity unless you hit the stop button. When
@i = 30, CONTINUE doesn't let the loop exit as shown in the result below
DECLARE @i INT = 10; WHILE @i <= 30 BEGIN SET @i = @i + 10; PRINT (@i); IF @i = 10 CONTINUE; END;
The following example demonstrates the nested loop.
DECLARE @i INT = 10, @j INT = 1; WHILE @i <= 30 BEGIN PRINT CONCAT ('i = ', @i); WHILE @j <=3 BEGIN PRINT CONCAT ('j = ', @j); SET @j = @j + 1; END SET @i = @i + 10; END;
In the above example, two variables
j are printed and incremented in two different WHILE loops. The outer WHILE loop executes the variable
i is less than or equal to 50. The inner loop is executed for every run of the outer loop for the condition
j <= 2.