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.

Syntax: 

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.

While Loop

The following example uses the WHILE loop to print numbers.

Example: WHILE Loop

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.

BREAK

The following example uses the BREAK keyword to exit from the loop.

Example: WHILE with BREAK

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.

CONTINUE

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

Example: WHILE with CONTINUE

DECLARE @i INT = 10;
 
WHILE @i <= 30
	BEGIN
   		SET @i = @i + 10;
		PRINT (@i); 
		
		IF @i = 10 CONTINUE;
	END;

Nested Loops

The following example demonstrates the nested loop.

Example: 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 i and 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.