Professional SQL Server™ 2005 Programming

10.5. Control-of-Flow Statements

Control-of-flow statements are a veritable must for any programming language these days. I can't imagine having to write my code where I couldn't change what commands to run depending on a condition.

Given that we're assuming at least an intermediate knowledge of both programming and SQL, we're not going to dwell on these a lot, but since "intermediate" means different things to different people, we had best give these the once over.

T-SQL offers most of the classic choices for control-of-flow situations, including:

• IF...ELSE

• GOTO

• WHILE

• WAITFOR

• TRY/CATCH

We also have the CASE statement (a.k.a. SELECT CASE, DO CASE, and SWITCH/BREAK in other languages), but it doesn't have quite the level of control of flow capabilities that you've come to expect from other languages.

10.5.1. The IF...ELSE Statement

IF...ELSE statements work much as they do in any language, although I equate them most closely to C in the way they are implemented. The basic syntax is:

IF <Boolean Expression>
   <SQL statement> | BEGIN <code series> END
[ELSE
   <SQL statement> | BEGIN <code series> END]

The expression can be pretty much any expression that evaluates to a Boolean.

This brings us back to one of the most common traps that I see SQL programmers fall into — improper user of NULLs. I can't tell you how often I have debugged stored procedures only to find a statement like:

IF @myvar = NULL

This will, of course, never be true on most systems (see the exception shortly) and will wind ...