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:

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.

 

 

 

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

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.