WHILE loop is the looping construct supported by Sql Server. Sql server doesn’t have for…loop, do…while loop etc, but with WHILE loop we can simulate these missing looping constructs behaviour.

This article will cover the following:

• Introduction to WHILE loop
• BREAK Statement
• CONTINUE Statement
• DO WHILE loop
• Looping through table records

WHILE LOOP

A while loop will check the condition first and then executes the block of Sql Statements within it as along as the condition evaluates to true.
Syntax:
WHILE Condition
BEGIN
Sql Statements
END

Example: Basic while loop example. The below while loop executes the statements within it 4 times.

DECLARE @LoopCounter INT = 1
WHILE ( @LoopCounter <= 4)
BEGIN
	PRINT @LoopCounter  
	SET @LoopCounter  = @LoopCounter  + 1
END	

RESULT:
1
2
3
4

BREAK Statement

If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop.

Example: WHILE loop with BREAK statement

DECLARE @LoopCounter INT = 1
WHILE ( @LoopCounter <= 4)
BEGIN
	PRINT @LoopCounter  
	IF(@LoopCounter = 2)
		BREAK
 	SET @LoopCounter  = @LoopCounter  + 1
END
PRINT 'Statement after while loop'

RESULT:

CONTINUE Statement

If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration.

Example: WHILE loop with CONTINUE statement

DECLARE @LoopCounter INT = 0
	WHILE ( @LoopCounter <= 3)
	BEGIN
		SET @LoopCounter  = @LoopCounter  + 1 

		IF(@LoopCounter = 2)
		  CONTINUE
 
		PRINT @LoopCounter 
	END
	PRINT 'Statement after while loop'

RESULT:

DO…WHILE Loop in Sql Server

Sql Server doesn’t have a DO…WHILE loop construct, but same behaviour can be achieved using a WHILE loop as shown by the below example.

DECLARE @LoopCounter INT = 1
WHILE(1=1)
BEGIN
   PRINT @LoopCounter
   SET @LoopCounter  = @LoopCounter  + 1 	
   IF(@LoopCounter > 4)
	BREAK;		   
END

RESULT:

Looping through table records one row at a time

Article Looping through table records in Sql Server lists out the various approaches of looping through table records row by row.

[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server