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. SET @COUNT=1
    4. 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. Result:
    9. 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. 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