Post
Ask Question

SET vs. SELECT when assigning values to variables

There are 2 ways of assigning a value to a variable, they are SET and the SELECT statements.

SET

  1. By using SET, we can only assign one variable at a time.

    Example:
    1. DECLARE @COUNT INT      
    2. DECLARE @INDEX INT      
    3.       
    4. SET  @COUNT=1      
    5. SET  @INDEX=2   
  2. If assigning a value using SET from a query returns no result, then SET will assign a NULL value to the variable.

    Example:
    1. DECLARE @EmpID VARCHAR(5)      
    2. SET @EmpID = '123'      
    3. SET @EmpID = (SELECT [EmpID]      
    4.               FROM [dbo].[Employee]      
    5.               WHERE [EmpID] = '321')      
    6. --Assume that there is no record with empid 321 in employee table      
    7. SELECT @EmpID     
    8.     
    9. Result:     
    10. NULL    
    Result:

    NULL

  3. If assigning a value using SET returns more than one value, SET will give an error.

    Example:
    1. DECLARE @EmpName VARCHAR(50)      
    2. SET @EmpName = 'Raj'      
    3. SET @EmpName = (SELECT [EmpName]      
    4.               FROM [dbo].[Employee]      
    5.               WHERE [EmpName] = 'Tej')      
    6. --Assume that there is multiple records with empname Tej in employee table      
    7. SELECT @EmpName    
    Result:

    Msg 512, Level 16, State 1, Line 3

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT

  1. By using SELECT we can assign values to more than one variable at a time.

    Example:
    1. DECLARE @COUNT INT        
    2. DECLARE @INDEX INT        
    3.         
    4. SELECT  @COUNT=1 , @INDEX=2   
  2. When assigning a value using SELECT from a query and the query returns no result, SELECT will not assign any value to the variable and therefore no change in the value of the variable.

    Example:
    1. DECLARE @EmpID VARCHAR(5)        
    2. SET @EmpID = '123'        
    3. SELECT @EmpID = [EmpID]        
    4.               FROM [dbo].[Employee]        
    5.               WHERE [EmpID] = '321'      
    6. --Assume that there is no record with empid 321 in employee table        
    7. SELECT @EmpID          

    Result:

    10. 123

  3. When assigning a value using SELECT from a query that returns more than one value, SELECT will assign the last value returned by the query.

    Example:
    1. DECLARE @EmpName VARCHAR(50)        
    2. SET @EmpName = 'Raj'        
    3. SELECT @EmpName = [EmpName]        
    4.               FROM [dbo].[Employee]        
    5.               WHERE [EmpName] = 'Tej'       
    6. --Assume that there is multiple records with empname Tej in employee table        
    7. SELECT @EmpName   
    Result:

    Tej
Next Recommended Reading Get Max and Min Integer Value Using Scalar-valued Function: