Sql-server – ‘Select @VAR =’ and ‘Set @VAR =’ behaving unexpectedly

sql serversql-server-2016

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.

Best Answer

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.

Related Solutions

Sql-server – Is execution of synonyms not logged in target database

The query is run in DatabaseA, not DatabaseB, so I'd say by design. That is, SQL Profiler captures "what is the connection database context" not "In what database is the object am I accessing"

This makes sense: each database is logically isolated from each other. Using a synonym or other cross database query is a special case because you can't ensure all databases are in synch (especially around restores) nor enforce foreign keys etc

An other example...

USE DatabaseD
GO
SELECT *
FROm
  DatabaseA.dbo.TableA A
  JOIN 
  DatabaseB.dbo.TableB B ON ..
  JOIN 
  DatabaseC.dbo.TableC C ON ..

It runs in DatabaseD so this is what you'd trap in profiler

Workaround? As far as I know (I haven't tried though) you can't distinguish the database of an accessed object, only of the connection.

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Best Answer

Related Solutions

Sql-server – Is execution of synonyms not logged in target database

Sql-server – Replace cursor with set-based approach

Related Question