Post
Ask Question

SET Vs SELECT When Assigning Values to Variables in SQL Server

Introduction
 
There are two ways to assign a value to a local variable created previously: SET and SELECT. We can usually use SET and SELECT alternatively without any effect. 
 
SET
 
A "SET" expression sets the specified local variable created previously to the given value.
 
Syntax
 
SET @localvariable = value
 
Here @localvariable is a local variable with any data type or cursor and value is any expression valid in SQL Server. After declaration, all variables are initialized with a NULL value. Using a SET statement we can assign a value to those variables. We must use a separate SET statement of each variable when there are multiple variables.
 
Examples
  1. --Simple example  
  2.   
  3. DECLARE @myTest VARCHAR(10)  
  4. SET @myTest = 'Jignesh'  
  5.   
  6. --Expression example  
  7.   
  8. DECLARE @a INT = 20;  
  9. DECLARE @b INT = 40;  
  10. DECLARE @c INT ;  
  11.   
  12. SET @c = @a + @b;  
SELECT
 
"SELECT" is designed to return row data. The main purpose of the "SELECT" statement is to retrieve a row from the database and allow the selection of one or more rows and columns from one or more database tables. We can also use a "SELECT" statement to assign a value to a local variable.
 
Syntax
 
SELECT @localvariable = value
 
Examples
  1. DECLARE @myTest VARCHAR(10)  
  2. SELECT @myTest = 'Jignesh'  
  3.   
  4. --Multiple assignment example  
  5.   
  6. DECLARE @A VARCHAR(10)  
  7. DECLARE @B VARCHAR(10)  
  8. DECLARE @C VARCHAR(10)  
  9.   
  10. SELECT @A='Jignesh', @B='Tejas', @C='Rakesh' 
SET SELECT
SET is ANSI standard for assigning a value to a local variable. SELECT is not an ANSI standard for assigning values to variables.
We can assign a value to one variable at a time.
 
Example
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
SET @a = 1;
SET @b = 2;
SET @c = 3; 
 
 We can assign a value to one or more variables at a time.
 
Example
 
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
SELECT @a = 5, @b = 6, @c = 7 
When assigning a level from a query to a variable and if the query does not return any results then the "SET" statement assigns a NULL value to the local variable.

Example
 
DECLARE @a VARCHAR(10)= 'jignesh'
SET @a = (SELECT CustomerCode FROM CustomerMasterWHERE 1 !=1)
PRINT @a 
When a value is assigned from the query to the variable and if the query does not return any results then the "SELECT" statement does not change the value of the local variable.
 
Example
 
DECLARE @b varchar(10)= 'jignesh'
SELECT @b = CustomerCodeFROM CustomerMasterWHERE 1 !=1
PRINT @b 
When a value is assigned from a query to a variable and if the query returns more than value then the "SET" statement raises an error.
 
DECLARE @a VARCHAR(10)= 'jignesh'
SET @a = (SELECT CustomerCode FROM CustomerMaster )
--Msg 512, Level 16, State 1, Line 2
--Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >=
or when the subquery is used as an expression. When value assigning
from the query to the variable and if query returns more than one
value than "SELECT" statement will assign the last value of query
result to the local variable. It does not raise any error. 
 
 When a value is assigned from a query to a variable and if the query returns more than value then the "SELECT" statement will assign the last value of the query results to the local variable. It does not raise an error.
SELECT is slightly faster than SET because SELECT is able to assign more than one variable at once whereas SET can assign one variable at once.  
 
Choose between SET and SELECT
 
"SET" will accept and assign a scalar value from the query while SELECT can accept multiple values from the query. But there is no way to determine which value is present in the variable after accepting multiple values using a SELELCT expression. With a SELECT expression, the last value populates from the returned list. Here the situation becomes unexpected because there is no warning and error whereas SET raises an error in this situation.
 
Use SELECT instead of SET when you need to populate multiple variables at once. SELECT requires a single statement to populate multiple variables whereas SET requires multiple statements.
 
The behavior of SET and SELECT expressions will be different when they are used with a query returning a null result. A SELECT expression would preserve the previous value of variable while the "SET" expression would set a null value in the above situation.
 
A SELECT expression may be a good choice in some scenarios, but SELECT is not ANSI standard. If you are strictly following standards then use SET instead of SELECT.
 
Summary
 
Depending on the scenario we may use either a SET or SELELCT expression.
 
My preference is to use a SELELCT expression in the following scenario:
  • Multiple variables are assigned a value from the query or table or assigned a value directly.
  • Less code for assigning a value to multiple variables.

We may use a SET expression in the following scenario:

  • If we want to assign a single value to a local variable
  • Need to follow ANSI Standards
  • NULL assignment to the variable is expected when null is returned in the result set
  • Exceptions can be accepted when multiple rows are returned from the result set.


Similar Articles