Controlling The Flow – Part 3

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.

The output of this program is shown below.

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?

Related posts

Leave a Comment