'Select @VAR =' and 'Set @VAR =' behaving unexpectedly

Question

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.

Answer 1

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.

Answer 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.