2

Working on a database i didn't setup myself i was digging through some stored procedures to find out what they were doing.

In here i found numerous variables being set through a SELECT @var =, I never really did such a thing and often only used SET @var =.

Reading up on the documentation Microsoft TSQL as well as Stackoverflow it seemed to me they are similar in their functionality yet not entirely.

So I decided to test it out with

DECLARE @var1 varchar(200)   
SELECT @var1 = 'Generic Name'   
SELECT @var1 = (SELECT Name FROM Users)   
SELECT @var1 AS 'Company Name'   

DECLARE @var2 varchar(30)   
SELECT @var2 = 'Generic Name'   
SET @var2 = (SELECT Name FROM Users)   
SELECT @var2 AS 'Company Name'

DECLARE @var3 varchar(200)   
SELECT @var3 = 'Generic Name'   
SELECT @var3 = Name FROM Users
SELECT @var3 AS 'Company Name'

DECLARE @var4 varchar(200)   
SELECT @var4 = 'Generic Name'   
SELECT @var4 = Name FROM Users WHERE Name = '2000'  
SELECT @var4 AS 'Company Name'

DECLARE @var5 varchar(200)   
SELECT @var5 = 'Generic Name'   
SELECT @var5 = (SELECT Name FROM Users WHERE Name = '2000') 
SELECT @var5 AS 'Company Name'

Based on the documentation i expected var1 to have the last value from the query and that it wouldn't throw an error. But var 1 and var 2 both throw the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The above result is expected for set based on the documentation, but not for assigning variable through select based on my understanding. Var3 does produce the expected result of taking the last name returned.

Similarly var4 and var5 behave weirdly. Neither query returns results, so i'd expect both to stay their old value of Generic Name. But instead only var4 keeps its old value. Var5 is set to NULL instead. And I don't understand why. To me it seems under some circumstances SELECT @var = behaves exactly like SET @var =, and in some it acts according to documentation.

  • Just to clarify about "last" - SQL Server is giving you an arbitrary row; do not make any assumptions about behavior because you think it is the last row from the table. You don't have an ORDER BY, so that is just the behavior SQL Server chose this time. It didn't have to choose that row, and any number of changes could make it not select the same "last" row next time. A table is an unordered bag of rows so there is no fixed definition of what "last" means anyway, in terms of the table, only in terms of the results returned which - without ORDER BY - are not in any fixed order. – Aaron Bertrand May 20 at 13:30
  • Only setting @var4 to NULL is weird in examples other results are correct and expected. – Denis Rubashkin May 20 at 13:42
  • @AaronBertrand, I can't, it doesn't seem possible. – Denis Rubashkin May 20 at 14:05
  • @AaronBertrand In my example, i indeed don't have an order by. Currently the 'last' record is simply the last added/last by PK. I have no different sorting applied. It however is consistent with documentation which says "If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.". – D Kramer May 20 at 14:31
  • 1
    Right I just want to make sure you distinguish "last value that is returned" from "last value in the table." Without order by, those could be different things, because the order the results are returned (so which row is returned first or last, for example) can vary based on a lot of factors. – Aaron Bertrand May 20 at 14:32
3

The main difference between SELECT and SET is that the former can perform multiple assignments simultaneously, e.g.

DECLARE @var1 int, @var2 int;

-- with SELECT, you can assign them together:
SELECT @var1 = 5, @var2 = 11;

-- with SET, must perform these separately:
SET @var1 = 5;
SET @var2 = 11;

Personally I would do this even more concisely, dropping both SET and SELECT:

DECLARE @var1 int = 5, @var2 int = 11;

Both SET and SELECT as assignment operators have the same restriction that, if you assign the result of a subquery, that subquery must return exactly one row, so you're not forcing SQL Server to try to determine which row you meant. This restriction is loosened up when no subquery is involved (and it works differently according to the standard, not some choice Microsoft made). So your first two cases work as designed and the error message is expected. @var3 and @var4 also both work as I would expect (though personally I would write this differently - e.g. if you want some concept of "last" row, tell SQL Server which "last" row you mean by using ORDER BY, MIN, MAX, etc.). As written, my results are:

@var3
----------------------------------------------------------------------
"Last" company name -- happens to be "last" in clustered index order,
                    -- but this might be different with other indexes
@var4
----------------------------------------------------------------------
Generic Name -- definitely not NULL

@var5 also works as expected. Yes, you assign a literal value first, but the assignment of the result of a subquery - in the absence of any error - overrides the previous value. In the case of an empty set, then it sets the value to NULL.

  • I agree that it's preferable to go a different route, but i was just trying to wrap my head around behaviour that i didn't know/couldn't explain. And still didn't entirely get based on the documentation. – D Kramer May 20 at 14:43
2

But var 1 and var 2 both throw the error

This is expected from that construct if more than one row is output from the sub-query. I think what you are expecting is what happens with SELECT @var1 = Name FROM Users - note that this no longer involves a sub-query.

When using this construct when not definitely expecting at most one record (for instance when query by a unique key) I would specifically ask for a given record, rather than leaving it to chance, either the first with SELECT TOP 1, the lowest/highest using the MIN()/MAX() aggregates, etc. Also always be sure (except for the aggregate method) to include an ordering or the value you get if there are more than one will be arbitrary and could potentially be different every time. Formatted the way it is in these examples the meaning when there are more than one matching row is ambiguous and I would want the server to error. The fact that examples like the setting @var3 one above do not return an error when multiple rows match is a bug IMO.

var4 and var5 behave weirdly

For @var5 this is the same issue. The sub-query is run and returns zero rows, as there is no value to set @var5 to is gets set to NULL.

The @Var4 example does seem odd, though it is an odd construct anyway. I've tested with the three main table types and I do not get the behaviour you describe, NULL being set, I instead get the variable left alone as I would have expected. This is with SQL Server 2017 though, I don't currently have a 2016 instance immediately available to test on.

  • @DavidSpillett Spillet Based on documentation from SELECT var =, i would expect a sub query that returns multiple rows to not be subject to the error as is thrown in the example of var1. But i suppose that this is simply I didn't know. – D Kramer May 20 at 14:50
  • In the SO response "If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable..." is referring to the query that is doing the assignment, the outer query, not the sub-query that is returning the values - so your third example rather than your first. I understand misunderstanding that way though as it is probably not obvious without prior experience. – David Spillett May 20 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.