Microsoft® SQL Server® 2012 Bible

394

Part IV: Programming with T-SQL

Result of the entire script:

----------- -------------------------

NULL NULL

(1 row(s) affected)

----------- -------------------------

1 a value

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2

Must declare the scalar variable "@Test".

The fi rst SELECT returns two NULL values. After the variables have been initialized, they

properly return the sample values. When the batch concludes (due to the GO terminator), so

do the variables. Error message 137 is the result of the fi nal SELECT statement.

Variables are local in scope and do not extend to other batches or called stored procedures.

Using the Set and Select Commands

Both the SET command and the SELECT command can assign the value of an expression to

a variable. The main difference between the two is that a SELECT can retrieve data from a

data source (for example, table, subquery, or view) and can include the other SELECT clauses

as well (for example, FROM, WHERE), whereas a SET is limited to retrieving data from expres-

sions. Both SET and SELECT can include functions. Use the simpler SET command when

you need to assign only a function result or constant to a variable and don’t need the Query

Optimizer to consider a data source. Additionally, SET can only set the value of one variable,

while multiple variable values may be set with a single SELECT statement.

A detailed exception to the preceding paragraph is when a SET command uses a scalar subquery

that accesses a data source. This is a best practice if you want to ensure that the variable is set

to NULL if no rows qualify, and that you get an error if more than one row qualifi es.

Of course, a SELECT statement may retrieve multiple columns. Each column may be

assigned to a variable. If the SELECT statement retrieves multiple rows, then the values

from the last row are stored in the variables. No error will be reported.

The following SQL batch creates two variables and initializes one of them. The SELECT

statement will retrieve multiple rows, ordered by ProductID. The ProductID and the

ProductName of the last product returned by the SELECT will be stored in the variables:

USE AdventureWorks2012;

GO

DECLARE @ProductID int,

c16.indd 394c16.indd 394 7/30/2012 5:38:05 PM7/30/2012 5:38:05 PM

http://www.it-ebooks.info