WHILE, BREAK, and CONTINUE Statements — Parv The IT Geek

Have you ever wondered how to create a loop in SQL? Or wondered how to break and escape a loop?

Well I am going to briefly introduce you to the WHILE, BREAK, and CONTINUE Statements which will satisfy your curiosity

The first command I will introduce you to is WHILE

DECLARE @i int = 1; 
WHILE @i < = 5 
BEGIN 
PRINT @i; 
SET @i = @i + 1; 
END

Loop

As you can see the WHILE statement will force the loop to continue until we reach 5. This is very useful if you need to batch process tasks and limit the amount done within each batch.

The next command to learn is CONTINUE. This command forces you to go back to the beginning of the loop.

DECLARE @i int = 1; 
WHILE @i < = 5 
BEGIN 
PRINT @i; 
SET @i = @i + 1; 
CONTINUE; -- This will cause the WHILE to loop back 
PRINT 'You wont see this due to the CONTINUE commands cleverness.'; 
END

Once the WHILE command is satisfied the CONTINUE command will allow the loop to complete.

The final command related to loops is the BREAK command.

DECLARE @i int = 1; 
WHILE @i < = 5 
BEGIN 
PRINT @i; 
SET @i = @i + 1; 
BREAK; -- Force the WHILE loop to terminate 
PRINT 'You wont see this due to the BREAK commands cleverness..'; 
END

As you can see from the example when the BREAK is encountered the loop is broken and it only ever prints 1.

In the real world many developers try not to use the BREAK and CONTINUE commands as they can be easily avoided in code and many people find it makes code less readable and unnecessarily complex.

---

Originally published at https://parvtheitgeek.com on March 15, 2015.