SQL Server: SET vs SELECT in Variable Assignment

Most often we are using SET and SELECT interchangeably while assigning values to variable, however there are differences between them. In this post we will discuss some diff between SET and SELECT.

• SET can assign value to only one variable at a time, where as SELECT can assign values to multiple variables at once.

Example

--Declare variable
DECLARE @myVar1 INT, @myVar2 VARCHAR(10);

--Assign value to variable using SET
SET @myVar1 = 1234;
SET @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2

--Assign value to variable using SELECT
SELECT @myVar1 = 1234, @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2

• SET operation will raise an error if when result of a query returning multiple values is used to assign value to a variable. However  SELECT will not raise any error and assign the last record of query result to the variable. So, Microsoft recommends SET for variable assignment

Example:

CREATE TABLE #TestVar(myID int, myComments VARCHAR(30))
INSERT INTO #TestVar VALUES(1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(4, 'E')

--Using SET
--Declare variable
DECLARE @myVar3 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SET @myVar3 = (SELECT myComments FROM #TestVar WHERE myID = 6)
--Extract varible values
SELECT @myVar3

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

--Using SELECT
--Declare variable
DECLARE @myVar4 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SELECT @myVar4 = myComments FROM #TestVar WHERE myID = 4;
--Extract varible values
SELECT @myVar4;

• In the above case if the query result will not return any values then the variable value will be NULL either using SELECT or SET.

• While talking in terms of performance variable assignment using SET will take less time as compared to SELECT.

Advertisement