SELECT vs SET For Variable Assignment In SQL Server

Using SELECT and SET statement for variable assignments is a common practice in SQL Server. I did not pay more attention to SELECT vs SET difference, until I met with surprising logical errors. In production code using SELECT over SET, produced unanticipated results. Both are used interchangeably for variable assignment. Importantly, understanding when to use SELECT over SET and vice versa.

In this tech-recipes post, an understanding of the difference between SELECT and SET statement. Moreover, walking through various examples involving both options. As a result, this will help you to come up with good practice and use appropriate option while variable assignment in your code as per need.

Points to Ponder – SELECT vs SET

1.SELECT can assign values to multiple variables in a single query. While SET is used for single variable assignment.

2.SET is ANSI standard, indicating other RDBMS are using a similar approach for variable assignment. As a standard practice. Whereas, the SELECT statement is used within SQL Server.

3.A variable value will be NULL in case the query doesn’t return anything while using SET in variable assignment. Whereas, SELECT will ignore the query result keeping variable value unchanged.

4.The SET statement fails if multiple rows are return by the query. In the case of SELECT, it assigns arbitrarily/ last value to variable ignoring the consequences.

Let’s walk through various examples to understand the difference between these two options. This will help you to make a decision based on your scenario to use SELECT or SET.

1

Single vs Multiple Variable Assignment using SELECT / SET

Using SELECT, we can assign values to multiple variables in a single statement. In the following example, we can see that @name and @object_id variable assigned values in a single line of code.

--Single vs Multiple value assignment using SEELCT/ SET
DECLARE @name as VARCHAR(255)
       ,@object_id as INT

select @name = name,
       @object_id = object_id
from   master.sys.all_objects where name = 'spt_values';

--Using SELECT
PRINT 'SEELCT - @name - ' + @name;
PRINT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10));
GO

Variable assignment using a SET statement expects us to mention each variable name individually. Using SET for multiple variable assignments is not allowed. Thus in the following example, we are using two SET options.

DECLARE @name as VARCHAR(255)
       ,@object_id as INT

--Using SET for individual variable assignment
SET @name = (SELECT name from master.sys.all_objects where name = 'spt_values');
SET @object_id = (SELECT object_id from master.sys.all_objects where name = 'spt_values');

--Using SELECT
PRINT 'SEELCT - @name - ' + @name;
PRINT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10));

2.

NULL Output – SELECT vs SET – Return Value

In the following example, the SELECT statement uses the default assigned values to variables even though the query has not returned any output.

Assigning @name = ‘Test’ and @object_id = 1000 as default values. Later trying to overwrite these values, however, the query doesn’t return any output thus variables are not overwritten. Although the SELECT statement retains the default values.

--Default values
DECLARE @name as VARCHAR(255) = 'Test'
       ,@object_id as INT = 1000

--No query output, thus variables are not assigned any values
select @name = name,
       @object_id = object_id
from   master.sys.all_objects where name = 'NOT_A_Table';

--Using SELECT, returns the old values instead of NULL.
SELECT 'SEELCT - @name - ' + @name as [@Name];
SELECT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10)) as [@ObjectId];
GO

Here, in below example, default values are overwritten. Because query doesn’t return any results thus SET statement assigns NULL to @name and @object_id variables.

--Default variable values
DECLARE @name as VARCHAR(255) = 'Test'
       ,@object_id as INT = 1000

--Using SET for individual variable assignment
SET @name = (SELECT name from master.sys.all_objects where name = 'NOT_A_Table');
SET @object_id = (SELECT object_id from master.sys.all_objects where name = 'NOT_A_Table');

--Using SELECT, returns NULL values instead of default values
SELECT 'SEELCT - @name - ' + @name as [@Name];
SELECT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10)) as [@ObjectId];
GO

3.

SET vs SELECT – Multiple Rows Output

Following example demonstrates the behaviour of a SELECT statement if multiple rows are returned by the query and used for variable assignment. If you run the following, subquery returning 3 rows as output.

Select name, object_id 
from   master.sys.all_objects 
where name IN ('spt_values', 'databases', 'sysservers');

Using SELECT to assign values to variables when a query returns multiple rows uses last return value. With SELECT, variables values are populated without giving away any warnings or error. Beware of this scenario as they can logically impact the code if these variables are used for future references.

--Default values
DECLARE @name as VARCHAR(255) = 'Test'
       ,@object_id as INT = 1000

--Actual 3 rows are returned
SELECT name, object_id
from   master.sys.all_objects where name IN ('spt_values', 'databases', 'sysservers');

--3 rows are returns, SELECT assigns last value to variables
select @name = name,
       @object_id = object_id
from   master.sys.all_objects where name IN ('spt_values', 'databases', 'sysservers');

--Using SELECT, returns the last values without any warning/error
SELECT 'SEELCT - @name - ' + @name as [@Name];
SELECT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10)) as [@ObjectId];
GO

Whereas, SET statement returns an error indicating subquery has return more than 1 row thus it’s unable to assign any random value to variables.

--Default variable values
DECLARE @name as VARCHAR(255) = 'Test'
       ,@object_id as INT = 1000

--3 rows are returns, SET generates an error
SET @name = (SELECT name from master.sys.all_objects where name IN ('spt_values', 'databases', 'sysservers'));
SET @object_id = (SELECT object_id from master.sys.all_objects where name IN ('spt_values', 'databases', 'sysservers'));

--Error
SELECT 'SEELCT - @name - ' + @name as [@Name];
SELECT 'SEELCT - @object_id - ' + CAST(@object_id as VARCHAR(10)) as [@ObjectId];
GO

Error

Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Summary

In summary, we have seen the difference between SELECT and SET statement. Using SELECT with multiple variable assignments, whereas SET doesn’t allow this. SELECT doesn’t return any NULL output in case query fails to return any output. But SET option assigns NULL to a variable. When Multiple rows are returned then SELECT randomly assigns the last value to a variable, whereas SET statement fails due to subquery returned more than 1-row error. If you like this post, you may read through tech-recipes database archives for more useful stuff.