T-SQL

WHILE, BREAK, and CONTINUE Statements

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

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

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

 

Loop
Loop

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.

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.