SELECT @local_variable (Transact-SQL)

Cikk
09/03/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Sets a local variable to the value of an expression.

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

Transact-SQL syntax conventions

Syntax

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

Arguments

@local_variable

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:

Operator	Action
=	Assigns the expression that follows, to the variable.
+=	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

Any valid expression. This includes a scalar subquery.

Remarks

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

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

Examples

A. Use 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 doesn't exist in the table. The variable retains the "Generic Name" value.

This example uses the AdventureWorksLT sample database, for more information, see AdventureWorks sample databases. The AdventureWorksLT database is used as the sample database for Azure SQL Database.

-- Uses AdventureWorks2022LT
DECLARE @var1 VARCHAR(30);
SELECT @var1 = 'Generic Name';

SELECT @var1 = [Name]
FROM SalesLT.Product
WHERE ProductID = 1000000; --Value does not exist
SELECT @var1 AS 'ProductName';

Here's the result set.

ProductName
------------------------------
Generic Name

B. Use SELECT @local_variable to return null

In the following example, a subquery is used to assign a value to @var1. Because the value requested for CustomerID doesn't exist, the subquery returns no value, and the variable is set to NULL.

-- Uses AdventureWorksLT
DECLARE @var1 VARCHAR(30);
SELECT @var1 = 'Generic Name';

SELECT @var1 = (SELECT [Name]
FROM SalesLT.Product
WHERE ProductID = 1000000); --Value does not exist

SELECT @var1 AS 'Company Name';

Here's the result set.

Company Name
----------------------------
NULL

C. Antipattern use of recursive variable assignment

Avoid the following pattern for recursive use of variables and expressions:

SELECT @Var = <expression containing @Var>
FROM
...

In this case, it isn't guaranteed that @Var would be updated on a row by row basis. For example, @Var may be set to initial value of @Var for all rows. This is because the order and frequency in which the assignments are processed is nondeterminant. This applies to expressions containing variables string concatenation, as demonstrated below, but also to expressions with non-string variables or += style operators. Use aggregation functions instead for a set-based operation instead of a row-by-row operation.

For string concatenation, instead consider the STRING_AGG function, introduced in SQL Server 2017 (14.x), for scenarios where ordered string concatenation is desired. For more information, see STRING_AGG (Transact-SQL).

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

An example to avoid, where using ORDER BY in attempt to order concatenation causes list to be incomplete:

DECLARE @List AS nvarchar(max);
SELECT @List = CONCAT(COALESCE(@List + ', ',''), p.LastName)
  FROM Person.Person AS p
  WHERE p.FirstName = 'William'
  ORDER BY p.BusinessEntityID;
SELECT @List;

Result set:

(No column name)
---
Walker

Instead, consider:

DECLARE @List AS nvarchar(max);
SELECT @List = STRING_AGG(p.LastName,', ') WITHIN GROUP (ORDER BY p.BusinessEntityID)
  FROM Person.Person AS p
  WHERE p.FirstName = 'William';
SELECT @List;