O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Procedural Flow

At first glance, it appears that T-SQL is weak in procedural-flow options. Although it's less rich than some other languages, it suffices. The data-handling boolean extensions — such as EXISTS, IN, and CASE — offset the limitations of IF and WHILE.

Using If for Conditional T-SQL

This is your grandfather's IF. The T-SQL IF command determines the execution of only the next single statement — one IF, one command. In addition, there's no THEN and no END IF command to terminate the IF block:

IF Condition
Statement;

In the following script, the IF condition should return a false, preventing the next command from executing:

IF 1 = 0

PRINT ‘Line One';
PRINT ‘Line Two';

Result:

Line Two
Note
The IF statement is not followed by a semicolon; in fact, a semicolon causes an error. That's because the IF statement is actually a prefix for the following statement; the two are compiled as a single statement.

Using Begin/End to Conditionally Execute Multiple Statements

An IF command that can control only a single command is less than useful. However, a BEGIN/END block can make multiple commands appear to the IF command as the next single command:

IF Condition
  Begin;
   Multiple lines;
  End;

Using If exists() as an Existence-Based Condition

While the IF command may seem limited, the condition clause can include several powerful SQL features similar to a WHERE clause, such as IF EXISTS() and IF. . .IN().

The IF EXISTS() structure uses the presence of any rows returned from a ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required