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
|
EmoticonEmoticon