Microsoft® SQL Server® 2012 Bible

397

Chapter 16: Programming with T-SQL

16

Using Variables within SQL Queries

Another feature of T-SQL is that variables may be used with SQL queries without having to

build any complex dynamic SQL strings to concatenate the variables into the code. Dynamic

SQL still has its place, but the single value can simply be modifi ed with a variable.

Anywhere an expression can be used within a SQL query, a variable may be used in its

place. The following code demonstrates using a variable in a WHERE clause:

USE AdventureWorks2012;

DECLARE @ProductID int = 999;

SELECT Name

FROM Production.Product

WHERE ProductID = @ProductID;

Result:

Name

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

Road-750 Black, 52

Debugging T-SQL

When a syntax error is found, the Query Editor displays the error and the line number of the error within

the batch. Double-clicking on the error message places the cursor near the offending line.

Often the error won’t occur at the exact word that is reported as the error. The error location reported

simply refl ects how far SQL Server’s parser got before it detected the error. Usually the actual error is

somewhere just before or after the reported error. Nevertheless, the error messages are generally close.

Multiple Assignment Variables

A multiple assignment variable, sometimes called an aggregate concatenation, is a fascinating

method that appends a variable to itself using a SELECT statement and a subquery.

This section demonstrates a real-world use of multiple assignment variables, but because

it’s an unusual use of the SELECT statement, here it is in its basic form:

SELECT @variable = @variable + d.column

FROM datasource d;

Each row from the derived table is appended to the variable, changing the vertical column

in the underlying table into a horizontal list.

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

http://www.it-ebooks.info