SQL Server – How to use WHILE loop with Break and Continue keyword

 
Most of developer know, how to use WHILE loop in sql server. You can control WHILE loop using CONTINUE and BREAK keyword. Whenever you need to stop the execution of while loop on certain condition, make use of BREAK keyword. It will exit from WHILE loop and control moved to next statement. CONTINUE keyword will skips all statement after CONTINUE keyword in WHILE loop
Lets go through with simple examples to understand WHILE loop functionality.

Example

Simple while loop example without break and continue keyword

declare @counter int
set @counter = 1
while (@counter <= 10)
begin
Print @counter
Set @counter = @counter + 2
end

Output

1
3
5
7
9

Example of WHILE loop with BREAK keyword

declare @counter int
set @counter = 1
while (@counter <= 10)
begin
if(@counter =1)
BEGIN
Print ‘Inside WHILE loop’
END
Print @counter
Set @counter = @counter + 2 — Increment by 2 to display odd number
If (@counter = 7)
BEGIN
Print @counter
Print ‘Exit from WHILE loop’
BREAK
END
end

Output

Inside WHILE loop
1
3
5
7
Exit from WHILE loop

Example of WHILE loop with CONTINUE keyword

declare @counter int
set @counter = 1
while (@counter <= 10)
begin
if(@counter =1)
BEGIN
Print ‘Inside WHILE loop’
END
Print @counter
Set @counter = @counter + 2 — Increment by 2 to display odd number
–Print ‘Continue WHILE loop’
CONTINUE — It will pass control to WHILE (@counter <=10)
If (@counter = 7) — This condition will never execute
BEGIN
Print ‘Exit from WHILE loop’
BREAK
END
end

Output

Inside WHILE loop
1
3
5
7
9

 

Leave a Reply

  

  

  


7 − = 5