Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Beals Conjecture - A Search for Counterexamples Using SQL Server
Tips & Tricks

Error Messages
Home > Tips & Tricks > SET vs. SELECT When Assigning Variables
SET vs. SELECT When Assigning Variables

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

Listed below are the differences between the SET and SELECT statements.

SET SELECT
ANSI standard for variable assignment. Non-ANSI standard when assigning variables.
Can only assign one variable at a time.
SET @Index = 1
SET @LoopCount = 10
SET @InitialValue = 5
Can assign values to more than one variable at a time.
SELECT @Index = 1, @LoopCount = 10,
       @InitialValue = 5

When assigning from a query and the query returns no result, SET will assign a NULL value to the variable.


DECLARE @CustomerID NCHAR(5)
SET @CustomerID = 'XYZ'
SET @CustomerID = (SELECT [CustomerID]
                   FROM [dbo].[Customers]
                   WHERE [CustomerID] = 'ABC')
SELECT @CustomerID -– Returns NULL
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.
DECLARE @CustomerID NCHAR(5)
SET @CustomerID = 'XYZ'
SELECT @CustomerID = [CustomerID]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ABC'
SELECT @CustomerID –- Returns XYZ
When assigning from a query that returns more than one value, SET will fail with an error.



SET = (SELECT [CustomerID]
       FROM [dbo].[Customers])

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.
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.
SELECT  @CustomerID = [CustomerID]
FROM [dbo].[Customers]
-- No error generated





Related Articles :