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