Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 345

Finally, run the following code to define a batch with an INSERT DEFAULT VALUES statement and 

to execute the batch 100 times.

INSERT INTO dbo.T1 DEFAULT VALUES; 

GO 100 

 

SELECT * FROM dbo.T1;

The query returns 100 rows with the values 1 through 100 in col1.

Flow Elements

Flow elements allow you to control the flow of your code. T-SQL provides very basic forms of control 

with flow elements, including the IF .

 

.

 

. ELSE element and the WHILE element. 

The 

IF .

 

.

 

. ELSE

 Flow element

The IF .

 

.

 

. ELSE element allows you to control the flow of your code based on a predicate. You specify 

a statement or statement block that is executed if the predicate is TRUE, and optionally a statement 

or statement block that is executed if the predicate is FALSE or UNKNOWN. 

For example, the following code checks whether today is the last day of the year (in other words, 

whether today’s year is different than tomorrow’s year). If this is true, the code prints a message say-

ing that today is the last day of the year; if it is not true (“else”), the code prints a message saying that 

today is not the last day of the year.

IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME())) 

  PRINT 'Today is the last day of the year.'; 

ELSE 

  PRINT 'Today is not the last day of the year.';

In this example, I use PRINT statements to demonstrate which parts of the code were executed and 

which weren’t, but of course you can specify other statements as well.

Keep in mind that T-SQL uses three-valued logic and that the ELSE block is activated when the 

predicate is either FALSE or UNKNOWN. In cases for which both FALSE and UNKNOWN are pos-

sible outcomes of the predicate (for example, when NULL marks are involved) and you need differ-

ent treatment for each case, make sure you have an explicit test for NULL marks with the IS NULL 

predicate. 

If the flow you need to control involves more than two cases, you can nest IF .

 

.

 

. ELSE elements. For 

example, the next code handles the following three cases differently: