|
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 :
|
|
|