The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable.
In most cases SET and SELECT may be used alternatively without any effect.
Following are some scenarios when consideration is required in choosing between SET or SELECT. Scripts using the AdventureWorks database are provided for further clarification.
Part 1 and 2 are mentioned in the scripts below. It would be better if you run each part of the script separately so you can see the results for each method.
Returning values through a query
Whenever you are assigning a query returned value to a variable, SET will accept and assign a scalar (single) value from a query. While SELECT could accept multiple returned values. But after accepting multiple values through a SELECT command you have no way to track which value is present in the variable. The last value returned in the list will populate the variable. Because of this situation it may lead to un-expected results as there would be no error or warning generated if multiple values were returned when using SELECT. So, if multiple values could be expected use the SET option with proper implementation of error handling mechanisms.
To further clarify the concept please run script # 1 in two separate parts to see the results
--Script# 1 - Using SET for assigning values USE AdventureWorks GO -- Part1. Populate by single row through SET DECLARE @Var1ForSet varchar(50) SET @Var1ForSet = (SELECT [Name] FROM Production.Product WHERE ProductNumber = 'HY-1023-70') PRINT @Var1ForSet GO -- Part 2. Populate by multiple rows through SET DECLARE @Var2ForSet varchar(50) SET @Var2ForSet = (SELECT [Name] FROM Production.Product WHERE Color = 'Silver') PRINT @Var2ForSet GO
Part 1 of the script should be successful. The variable is populated with a single value through SET. But in part 2 of the script the following error message will be produced and the SET statement will fail to populate the variable when more than one value is returned.
Error message generated for SET
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Hence SET prevented assignment of an ambiguous value.
In case of SELECT, even if multiple values are returned by the query, no error will be generated and there will be no way to track that multiple values were returned and which value is present in the variable. This is demonstrated in the following script.
--Script # 2 - Using SELECT for assigning values USE AdventureWorks GO -- Part1. Populate by single row through SELECT DECLARE @Var1ForSelect varchar(50) SET @Var1ForSelect = (SELECT [Name] FROM Production.Product WHERE ProductNumber = 'HY-1023-70') PRINT @Var1ForSelect GO -- Part2. Populate by multiple rows through SELECT DECLARE @Var2ForSelect varchar(50) SELECT @Var2ForSelect = [Name] FROM Production.Product WHERE Color = 'Silver' PRINT @Var2ForSelect GO
Both part 1 and 2 were executed successfully. In part 2, multiple values have been assigned and accepted, without knowing which value would actually populate the variable. So when retrieval of multiple values is expected then consider the behavioral differences between SET and SELECT and implement proper error handling for these circumstances.
Assigning multiple values to multiple variables
If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database.
Consider the following script comparing the use of SELECT and SET.
--Script # 3 - Populating multiple variables through SELECT USE AdventureWorks GO -- Part 1. Assign direct values to multiple variables DECLARE @var1 VARCHAR(50) DECLARE @var2 VARCHAR(50) DECLARE @var3 VARCHAR(50) SELECT @var1 = 'Value1', @var2 = 'Value2', @var3 = 'Value3' PRINT @var1 PRINT @var2 PRINT @var3 GO -- Part 2. Assign retrieved values to multiple variables DECLARE @name VARCHAR(50) DECLARE @productNo VARCHAR(25) DECLARE @color VARCHAR(15) SELECT @name = [Name], @productNo = ProductNumber, @color = Color FROM Production.Product WHERE ProductID = 320 PRINT @name PRINT @productNo PRINT @color GO
If you are using SET then each variable would have to be assigned values individually through multiple statements as shown below.
--Script # 4 - Populating multiple variables through SET USE AdventureWorks GO -- Part 1. Assign direct values to multiple variables DECLARE @var1 VARCHAR(50) DECLARE @var2 VARCHAR(50) DECLARE @var3 VARCHAR(50) SET @var1 = 'Value1' SET @var2 = 'Value2' SET @var3 = 'Value3' PRINT @var1 PRINT @var2 PRINT @var3 GO -- Part 2. Assign retrieved values to multiple variables DECLARE @name VARCHAR(50) DECLARE @productNo VARCHAR(25) DECLARE @color VARCHAR(15) SET @name =(SELECT [Name] FROM Production.Product WHERE ProductID = 320) SET @productNo = (SELECT ProductNumber FROM Production.Product WHERE ProductID = 320) SET @color = (SELECT Color FROM Production.Product WHERE ProductID = 320) PRINT @name PRINT @productNo PRINT @color GO
Obviously SELECT is more efficient than SET while assigning values to multiple variables in terms of statements executed, code and network bytes.
What if variable is not populated successfully
If a variable is not successfully populated then behavior for SET and SELECT would be different. Failed assignment may be due to no result returned or any non-compatible value assigned to the variable. In this case, SELECT would preserve the previous value if any, where SET would assign NULL. Because of the difference functionality, both may lead to unexpected results and should be considered carefully.
This is shown in following script
--Script # 5 - Behavior of SET and SELECT for missing value USE AdventureWorks GO -- Part 1. Observe behavior of missing result with SET DECLARE @var1 VARCHAR(20) SET @var1 = 'Value 1 Assigned' PRINT @var1 SET @var1 = (SELECT Color FROM Production.Product WHERE ProductID = 32022) PRINT @var1 GO -- Part 1. Observe behavior of missing result with SELECT DECLARE @var1 VARCHAR(20) SELECT @var1 = 'Value 1 Assigned' PRINT @var1 SELECT @var1 = Color FROM Production.Product WHERE ProductID = 32023 PRINT @var1 GO
We can see that part 1 generates NULL when no value is returned for populating variable. Where as part 2 produces the previous value that is preserved after failed assignment of the variable. This situation may lead to unexpected results and requires consideration.
Following the standards
Using SELECT may look like a better choice in specific scenarios, but be aware that using SELECT for assigning values to variables is not included in the ANSI standards. If you are following standards for code migration purposes, then avoid using SELECT and use SET instead.
Best practice suggests not to stick to one method. Depending on the scenario you may want to use both SET or SELECT.
Following are few scenarios for using SET
- If you are required to assign a single value directly to variable and no query is involved to fetch value
- NULL assignments are expected (NULL returned in result set)
- Standards are meant to be follow for any planned migration
- Non scalar results are expected and are required to be handled
Using SELECT is efficient and flexible in the following few cases.
- Multiple variables are being populated by assigning values directly
- Multiple variables are being populated by single source (table , view)
- Less coding for assigning multiple variables
- Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed
- Click here to look at assigning and declaring variables through single statement along with other new T-SQL enhancements in SQL Server 2008.
- Click here to read more about @@ROWCOUNT
- Click here to read more about @@ERROR
Last Update: 2009-11-25
About the author
View all my tips