Microsoft® SQL Server® 2012 Bible

399

Chapter 16: Programming with T-SQL

16

An alternative method to denormalize a list is the XML PATH method:

SELECT [text()] = Name + ','

FROM (

SELECT DISTINCT Name

FROM HumanResources.Department) d

ORDER BY Name

FOR XML PATH('');

For more details on XML, see Chapter 14, “Using XML Data.”

Procedural Flow

At fi rst glance, it appears that T-SQL is weak in procedural-fl ow options. Although it’s less

rich than some other languages, it suffi ces. 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 com-

mand from executing:

IF 1 = 0

PRINT 'Line One';

PRINT 'Line Two';

Result:

Line Two

The IF statement is not followed by a semicolon; in fact, a semicolon causes an error. That’s because the IF state-

ment is actually a prefi x 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:

c16.indd 399c16.indd 399 7/30/2012 5:38:07 PM7/30/2012 5:38:07 PM

http://www.it-ebooks.info