There are 2 ways of assigning a value to a local variable previously created with the DECLARE @LocalVariable statement, namely using the SET and the SELECT statements. To illustrate:
DECLARE @SETVariable INT, @SELECTVariable INT
SET @SETVariable = 1
SELECT @SELECTVariable = 2
Here are the differences between the SET and SELECT statements:
SET Statement
• ANSI standard for variable assignment
• Can only assign one variable at a time
• When assigning from a query and the query returns no result, SET will assign a NULL value to the variable.
• When assigning from a query that returns more than one value, SET will fail with an error.
SELECT Statement
• Non-ANSI standard when assigning variables.
• Can assign values to more than one variable at a time.
• When assigning from a query and the query returns no result, SELECT will not make the assignment and therefore not change the value of the variable.
• When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row.