Set vs Select – Assigning variables

SET and SELECT both can be used to assign values to the local variables in SQL Server. However, SET is an ANSI standard and also recommended by Microsoft to be used for variable assignment. In this post, “Set vs Select – Assigning variables“, we will explore the behavior of SET and SELECT statement during variable assignment from a query.

When we use a scalar subquery (which returns a single value) to assign a local variable using SET or SELECT, both statements behave similar. It means, if subquery returns no rows, they set variable to NULL during variable assignment. But when a column name from a query (which is not a scalar subquery) directly assigns to a local variable using SELECT statement and the query returns 0 rows, then the variable value remains unchanged.

As per its nature, SET statement uses a scalar subquery to assign a local variable from a query. A scalar subquery returns exactly one column value from one row and in case subquery returns no rows, scalar subquery returns a NULL value which gets assigned to the variable. However, if subquery returns multiple rows, it throws an error.

When SELECT statement uses a scalar subquery to assign a local variable, in this case the behavior of SELECT statement is similar to the SET statement. However, when SELECT statement uses a column from a query (which is not a scalar subquery) to assign a local variable, in this case if there is no record being received from the query, the variable remains unchanged with its initial value, and if the query returns multiple values, instead of throwing an error, it simply assigns the last value of the row to the variable.

Lets summarize the behavior of SET and SELECT statements during variable assignment from a query:

SET Statement

1. It can assign the value of an expression to a local variable. This is an ANSI standard and Microsoft recommends it for variable assignment.
2. A scalar subquery can be used to assign a value to a local variable from a query. In this case:
   • If query returns 0 rows, it sets the variable to NULL because scalar subquery returns a NULL value in case no record is being returned from the query.
   • In case query returns multiple rows, it throws an error.

SELECT Statement

1. It can assign the value of an expression to a local variable
2. A scalar subquery can be used to assign a local variable. In this case:
   • If query returns 0 rows, like SET statement, it sets the variable to NULL because scalar subquery returns a NULL value in case no record is being returned from the query.
   • In case the query returns multiple rows, it throws an error.
3. A column name from a query can also be used to assign a local variable (without using a scalar subquery). In this case:
   • If query returns 0 rows, the variable remains unchanged with its initial value
   • If query returns multiple values, it simply assigns the last value of the result set to the variable instead of throwing an error.

To understand this, lets have a demo:

Firstly, create a dummy table with few records to understand this in more detail.

CREATE TABLE #Tbl
(
 Col INT
)

--Insert few rows
INSERT INTO #Tbl (Col)
SELECT 1 AS Col UNION ALL SELECT 2 UNION ALL SELECT 3

--Select records from table
SELECT Col FROM #Tbl

Below is the table with its sample data:

Assigning variable from a query which returns no rows

To understand the behavior of SET and SELECT statements during variable assignment from a scalar subquery (which returns no rows) and from a column name of a query (which returns no rows), have a look on the below queries and their outputs:

--Query 1 - SET statement asigning variable with scalar subquery
DECLARE @Var1 INT = 0
SET @Var1 = (SELECT Col FROM #Tbl WHERE Col = 0)
SELECT @Var1 AS VarValue1

--Query 2 - SELECT statement assigning variable with scalar subquery
DECLARE @Var2 INT = 0
SELECT @Var2 = (SELECT Col FROM #Tbl WHERE Col = 0)
SELECT @Var2 AS VarValue2

--Query 3 - SELECT statement assigning variable directly with column name
DECLARE @Var3 INT = 0
SELECT @Var3 = Col FROM #Tbl WHERE Col = 0
SELECT @Var3 AS VarValue3

Output:

In above image, in case of scalar subquery which returns no rows, the variable has been assigned to NULL by SET and SELECT both statements (Query 1 and Query 2). However, in case of assigning value directly from a column name from a query using SELECT statement, the value of the variable remains unchanged if query returns no rows (Query 3).

Assigning variable from a query which returns multiple rows

Lets assign a local variable from a query which returns multiple rows using SET and SELECT statements.

--Query 1 - SET statement assigning variable with multi value subquery
DECLARE @Var4 INT = 0
SET @Var4 = (SELECT Col FROM #Tbl)
SELECT @Var4 AS VarValue1

--Query 2 - SELECT statement assigning variable multi value subquery
DECLARE @Var5 INT = 0
SELECT @Var5 = (SELECT Col FROM #Tbl)
SELECT @Var5 AS VarValue2

--Query 3 - SELECT statement assigning variable directly with column name using multi value query
DECLARE @Var6 INT = 0
SELECT @Var6 = Col FROM #Tbl
SELECT @Var6 AS VarValue3

Output:

In above image, in case the subquery returns multiple rows, an error has been thrown by SET and SELECT both statements (Query 1 and Query 2). However, in case of assigning value directly from a column name from a query using SELECT statement, the value of the variable gets assigned with the last value of the row (Query 3).

Thank you for the reading. Please share your inputs in comment section of this article and also share this post on your social media.

Below are the references:

SET @local_variable (Transact-SQL)

SELECT @local_variable (Transact-SQL)