When to use SET vs SELECT when assigning values to variables in SQL Server

By:   |   Updated: 2009-11-25   |   Comments (13)   |   Related: > TSQL


Problem

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.

Solution

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

Msg 512, Level 16, State 1, Line 4
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.

Conclusion

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
Next Steps
  • 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-11-25

Comments For This Article




Sunday, November 5, 2017 - 4:40:58 PM - Richard Abey-Nesbit Back To Top (69251)

 

@Bryant McClellan - I don't think Atif is saying that the ambiguity problem is made "okay" by the efficenecy gain of assigning multiple variables in one select statement - it's still a potential issue. But development time is a scarce resource. As long as you're aware of the potential issue and put some thought into making sure that it won't cause a problem in your use case - or have taken steps to mitigate any danger if it is present - you're likely better off using SELECT and making use of the time it saves you to check for issues in other areas of your code.

It's just a tradeoff. Writing SELECT vs SET for assigning a single variable based on a query doesn't really save you or the processor much time (in most cases), so comply with the standard because the cost is so negligible. But when the cost starts getting high - like when you're assigning values to 10 variables from the same query - it can become worth it to intentionally deviate from the standard.


Tuesday, March 21, 2017 - 8:05:26 AM - Bryant McClellan Back To Top (51497)

Switching to SELECT to make multiple assignments makes the ambiguity problem worse. While SELECT will likely always be successful, SET will fail if there are multiple results. Is the author saying that the ambiguity problem is OK if it is multiple variables to assign just becasue SELECT is faster? Would you not want to guarantee that each variable held the proper value and the code fails if it does not?

Using SET over SELECT is an example of defensive programming. You have to ask yourself if the problem of having wrong data in a variable is ok as long as the performance is better. IMHO that answer is a resounding NO.

 


Thursday, January 28, 2016 - 6:00:40 AM - dhinesh Back To Top (40525)

 

Kindly let us know how to give input dynamically in sql?

declare

a integer;

b integer

c integer

begin

????????????????

c:=a+b;

end


Friday, July 11, 2014 - 6:49:08 AM - Qaiser Back To Top (32653)

Very useful information, atleast i have learned something new today. Thanks


Friday, May 2, 2014 - 10:30:34 AM - Fool4UAnyway Back To Top (30597)

"Select is about 59% faster than a Set and for one to a handful of value assignments that's not a problem and the standard should rule."

This is not correct.

SELECT takes 59% less time than SET.

SELECT is about 2.41 times as fast as SET, so it is about 141% faster.

 


Friday, May 2, 2014 - 9:52:36 AM - Fool4UAnyway Back To Top (30595)

"Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed"

 

It's @@ERROR, of course.

 

@@ERROR is cleared upon reading its value, but I haven't read that the same goes for @@ROWCOUNT.

 

 -- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @ErrorVar = @@ERROR
    ,@RowCountVar = @@ROWCOUNT;

The text above is shown for @@ERROR, but the description of @@ROWCOUNT doesn't mention anything like this at all.

 

However, it is, of course, good practice to capture both values using a SELECT statement if you want to retrieve them simultaneously.

 


Thursday, March 27, 2014 - 5:58:17 AM - wasim zafri Back To Top (29897)

very  nice ..... i  liked it


Wednesday, November 20, 2013 - 12:14:41 PM - ShaluDavid Back To Top (27551)

Nice article! Thanks..


Thursday, February 7, 2013 - 12:48:15 PM - naveen Back To Top (21991)

I used interchangeably so far. Suprised to know these differences and excellent articulation


Thursday, January 31, 2013 - 9:17:06 AM - Hugo Mendes Back To Top (21836)

Great article, very well explained. Thanks.


Wednesday, January 23, 2013 - 10:07:39 AM - Ed - sqlscripter Back To Top (21647)

Nice article, I always wondered the difference but never tested it. If I am looping through a cursor ( for admin purposes not application data purposes ) I do a Select @variable = '' to clear it also before populating it again.


Monday, October 10, 2011 - 8:46:10 AM - Azim (PRAL) Back To Top (14821)

Very nice blog, infect we addressed one of our recent issue by replacing set with select but was not aware why set was assigning null values occasionally, now the reasons got cleared.


Friday, January 8, 2010 - 5:31:25 PM - DonWert Back To Top (4688)

Good article.  The caveat about what happens when the variable is not populated successfully can't be overstated.  The fact that a failed Select leaves the previous value in place has some serious implications for the code unless the developer gives the variable a default value and checks the value after the Select statement to ensure that it has, indeed, changed.  I've been bitten by that particular issue on occasion when I forgot to add such a safeguard - the code ran and returned expected results the first time around but not in later tests.  It introduces a subtle bug to the code that can be really hard to track down.

Performance of Set vs Select has to be taken into consideration when deciding which to use.  Unless the design has changed, using a Set executes an assignation language element (SQL Server Magazine Reader-to-Reader tip February 2007 here).  According to the tip, a Select is about 59% faster than a Set and for one to a handful of value assignments that's not a problem and the standard should rule.  However, what if you are assigning the variable's value repeatedly in a loop that will run thousands of times?  Hopefully you won't since that solution is most likely not set-based, but if that's the only way to address a particular problem you might do it.  In such a case, it would be worthwhile deviating from the ANSI standard.















get free sql tips
agree to terms