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