Satheesh Kannan Chandra's Blog

To assign a value to a variable we use either SELECT or SET statement based on our preference. Is there any difference between the two assignment statements? I can see few differences when using these two statements. They are:

• We can use SELECT statement when we assign values to more than one variable. However SET can be used to assign value to one variable at a time.
• SELECT statement works well with record sets, hence it won’t throw any error when we assign a record set value to a variable. Whereas SET statement throws an error if we try to assign record set value to a variable if the record set returns more than one value.
  
  USE northwind
  GO
  DECLARE @selectVar varchar(40), @setVar varchar (40)
  SELECT @selectVar = LastName FROM Employees
  SELECT @selectVar
  SET @setVar = (SELECT lastname FROM Employees WHERE EmployeeID = 1)
  SELECT @setVar
  
  In the above code, SELECT assignment statement accepts the record set and assigns the last value to the variable selectVar. However, if we use SET statement without limiting the result of the SELECT statement to a single value (by using where clause), then the SET statement returns an error.
• SET statement is ANSI standard where SELECT is not.

# posted by Satheesh Chandra @ 7:06 PM