Microsoft® SQL Server® 2012 Bible

395

Chapter 16: Programming with T-SQL

16

@ProductName varchar(25);

SET @ProductID = 782;

SELECT

@ProductID = ProductID,

@ProductName = Name

FROM Production.Product

ORDER BY ProductID;

SELECT @ProductID as ProductID, @ProductName as ProductName;

Result:

ProductID ProductName

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

999 Road-750 Black, 52

The preceding code demonstrates a common coding mistake. Never use a SELECT to populate a variable unless

you’re sure that it will return only a single row.

If no rows are returned from the SELECT statement, the SELECT does not affect the vari-

ables. In the following query, there is no product with a ProductID of 999 , so the SELECT

statement does not affect either variable:

USE AdventureWorks2012;

GO

DECLARE @ProductID int,

@ProductName varchar(25);

SET @ProductID = 999;

SELECT

@ProductID = ProductID,

@ProductName = Name

FROM Production.Product

WHERE ProductID = 1000;

SELECT @ProductID as ProductID, @ProductName as ProductName;

The fi nal SELECT statement reports the value of @ProductID and @ProductName, and

indeed they are still 999 and NULL, respectively. The fi rst SELECT did not alter its value:

ProductID ProductName

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

999 NULL

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

http://www.it-ebooks.info