While Loop in SQl Server

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 .
This article will cover the following:
1.    Introduction to WHILE loop
2.    BREAK Statement
3.    CONTINUE Statement
4.    DO WHILE loop
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 @LoopNo INT = 1
WHILE ( @LoopNo <= 4)
BEGIN
    PRINT @LoopNo 
    SET @LoopNo  = @LoopNo  + 1
END



Run the above script and See the Output


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 @LoopNo INT = 1
WHILE ( @LoopNo <= 4)
BEGIN
    PRINT @LoopNo 
    IF(@LoopNo = 2)
        BREAK
    SET @LoopNo  = @LoopNo  + 1
END
PRINT 'Statement after while loop'




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 @LoopNo INT = 0
    WHILE ( @LoopNo <= 3)
    BEGIN
        SET @LoopNo  = @LoopNo  + 1

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




DO…WHILE Loop in Sql Server
Sql Server doesn’t have a DO…WHILE loop construct, but same behavior can be achieved using a WHILE loop as shown by the below example.
DECLARE @LoopNo INT = 1
WHILE(1=1)
BEGIN
   PRINT @LoopNo
   SET @LoopNo  = @LoopNo  + 1   
   IF(@LoopNo > 4)
    BREAK;         
END





Share this

Related Posts

Previous
Next Post »