Export (0) Print
Expand All
4 out of 10 rated this helpful - Rate this topic

SELECT @local_variable (Transact-SQL)

Specifies that the specified local variable that is created by using DECLARE @local_variable should be set to the specified expression.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable. For more information, see SET @local_variable.

Topic link icon Transact-SQL Syntax Conventions

SELECT { @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression } [ ,...n ] [ ; ]
@local_variable

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

=

Assign the value on the right to the variable on the left.

{= | += | -= | *= | /= | %= | &= | ^= | |= }

Compound assignment operator:

+= Add and assign

-= Subtract and assign

*= Multiply and assign

/= Divide and assign

%= Modulo and assign

&= Bitwise AND and assign

^= Bitwise XOR and assign

|= Bitwise OR and assign

expression

Is any valid expression. This includes a scalar subquery.

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is 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.

One SELECT statement can initialize multiple local variables.

Note Note

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

A. Using SELECT @local_variable to return a single value

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

USE AdventureWorks2012 ;       
GO       
DECLARE @var1 nvarchar(30);       
SELECT @var1 = 'Generic Name';       
SELECT @var1 = Name       
FROM Sales.Store       
WHERE CustomerID = 1000 ;      
SELECT @var1 AS 'Company Name';

Here is the result set.

Company Name

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

Generic Name

B. Using SELECT @local_variable to no result set returns null

In the following 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 AdventureWorks2012 ; 
GO 
DECLARE @var1 nvarchar(30) 
SELECT @var1 = 'Generic Name' 
SELECT @var1 = (SELECT Name 
FROM Sales.Store 
WHERE CustomerID = 1000) 
SELECT @var1 AS 'Company Name' ;

Here is the result set.

Company Name

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

NULL

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.