SELECT @local_variable (T-SQL)

Specifies that the given local variable (created using DECLARE @local_variable) should be set to the specified expression.

It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.

Syntax

SELECT {@local_variable = expression } [,...n]

Arguments

@local_variable

Is a declared variable for which a value is to be assigned.

expression

Is any valid Microsoft® SQL Server™ expression, including a scalar subquery.

Remarks

SELECT @local_variable is usually used to return a single value into the variable; however, it can return multiple values, for example, if expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

In the first example, a variable @var1 is assigned Generic Name as its value. The query against the Customers table returns no rows because the value specified for CustomerID does not exist in the table. The variable retains the Generic Name value.

USE Northwind

DECLARE @var1 nvarchar(30)

SELECT @var1 = 'Generic Name'

SELECT @var1 = CompanyName
FROM Customers
WHERE CustomerID = 'ALFKA'

SELECT @var1 AS 'Company Name'

This is the result:

Company Name

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

Generic Name

In this example, a subquery is used to assign a value to @var1. Because the value requested for CustomerID does not exist, the subquery returns no value and the variable is set to NULL.

USE Northwind

DECLARE @var1 nvarchar(30)

SELECT @var1 = 'Generic Name'

SELECT @var1 =
    (SELECT CompanyName
    FROM Customers
    WHERE CustomerID = 'ALFKA')

SELECT @var1 AS 'Company Name'

This is the result:

Company Name

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

NULL

One SELECT statement can initialize multiple local variables.

Note A SELECT statement that contains a variable assignment cannot also be used to perform normal result set retrieval operations.

See Also