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.
-- Count to 256
-- Loop counter
DECLARE @VAR_CNT INT = 1;
-- Infinite loop
WHILE (1 = 1)
-- Show counter
-- Increment counter
SET @VAR_CNT += 1;
-- Exit loop or continue;
IF (@VAR_CNT > 256)
-- Never happens
PRINT 'Never Never Land'
The output of this program is shown below.
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?