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.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: