Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between SET and SELECT When Assigning Variables
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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.

Back to Tip of the Day List Next Tip