SET and SELECT Differences

- SET is the ANSI standard for variable assignment, SELECT is not.

- SELECT can be used to assign values to more than one variable at a time. SET can only assign a value to one variable at a time.

- When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

- When assigning a variable from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (the variable will have its' previous value if it has been assigned before).

- Be careful with SET and CASE WHEN statement. When any conditions do not match, CASE WHEN will return ‘NULL’ if ELSE is not specified. Therefore the variable will have a ‘NULL’ value.

DECLARE @errorCode AS INTEGER 
SET @errorCode = 0	
SET @errorCode = CASE
				 WHEN 'A'='B'
				 THEN 1000
				 END
SELECT @errorCode
-- @errorCode will return NULL

- Always use ‘SELECT’ instead of ‘SET’ to get @@ERROR and @@ROWCOUNT

SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

Related article:
http://vyaskn.tripod.com/differences_between_set_and_select.htm