I am going to continue my articles (tidbits) on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.
The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.
Today, I want to talk about how to use the BREAK and CONTINUE statements.
I hardly ever use these statements during my coding adventures. However, they are handy for maintaining control of an infinite loop.
The example below will loop forever if they are not used. If they are used, the example will count from one to two-hundred-fifty-six.
The CONTINUE statement transfers code execution to the outer loop. Thus, the ‘Peter Pan’ reference is never shown.
The BREAK statement exits the current execution loop. Thus the infinite loop is terminated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- -- Count to 256 -- -- Loop counter DECLARE @VAR_CNT INT = 1; -- Infinite loop WHILE (1 = 1) BEGIN -- Show counter PRINT @VAR_CNT; -- Increment counter SET @VAR_CNT += 1; -- Exit loop or continue; IF (@VAR_CNT > 256) BREAK ELSE CONTINUE; -- Never happens PRINT 'Never Never Land' END GO |
The output of this program is shown below.
|
1 2 3 4 5 6 7 8 9 10 11 |
output: 1 2 3 (...) 254 255 256 |
Please note, the use of the IF/ELSE statement. Either we continue counting if we have not reached our exit point or we break the current execution.
To wrap up this talk, the BREAK/CONTINUE commands are good for controlling the execution flow of the current block of statements.
Next time, I will cover the GOTO statement. Most people will agree that this is bad programming style due to the fact that complexity can become unmanageable with nesting.
However, scripting any existing job within Microsoft SQL Server Management Studio will generate code that uses this statement to control error handling. I hope this changes in the next release of SQL Server!
Why not TRY/CATCH for error handling?
