Just a short blog post to demonstrate the use of a WHILE loop in T-SQL, together with the BREAK and CONTINUE statements.
A WHILE loop executes a statement block repeatedly until a specified condition is no longer true. Inside the loop, the execution can be controlled with the BREAK and CONTINUES statements. BREAK causes an exit from the loop and CONTINUE causes the loop to restart and move to the next iteration.
In the T-SQL code below, a loop increments an value (@COUNTER) from 0 to 10 and prints it. At 7, the CONTINUE statement is demonstrated. 7 is never printed, since CONTINUE restarts the loop. At 9, the BREAK statement is demonstrated. 9 and 10 is never printed, since BREAK exits the loop.
DECLARE @COUNTER INT = 0 WHILE @COUNTER < 10 BEGIN SET @COUNTER = @COUNTER + 1 IF @COUNTER = 7 CONTINUE IF @COUNTER = 9 BREAK PRINT @COUNTER END
A common scenario where BREAK and CONTINUE can be used is together with error handling in a loop, when you for instance just want to move to the next iteration in case of an error. Or perhaps exit the loop completely when an error occurs, not iterating through the rest.
Thanks
Simple but clear
Good example. Thanks!
it might be best to have the WHILE condition check for < 15, to better illustrate a break.
If you leave it at < 10, the break does exactly the same thing as CONTINUE, since the @COUNTER on the next iteration would now be = 10 and the loop would stop anyway.