Different behaviors between select to variable in SQL
I encountered different behaviors between the two syntaxes of select to variable in SQL.
The first type of select has the following syntax:
SELECT @Variable = C FROM T
and the second syntax:
SET @Variable = (SELECT C FROM T)
When the query result is a single value the behavior of both is the same (as expected the query result will be placed into the variable).
Things become more difficult when the query returns more than one result, or zero results as can be seen in the following examples:
For the first syntax:
--ZERO RESULTS
DECLARE @answer int
SET @answer = 42
SELECT @answer = object_id FROM sys.objects WHERE 1=0
IF (@answer = 42)
BEGIN
PRINT 'Answer to the Ultimate Question of Life, the Universe, and Everything is: 42'
END
ELSE
BEGIN
IF (@answer IS NULL)
PRINT 'NULL'
ELSE PRINT CONVERT(char(20), @answer)
END
--MUTLIPLE RESULTS
SET @answer = 42
SELECT @answer = object_id FROM sys.objects
IF (@answer = 42)
BEGIN
PRINT 'Answer to the Ultimate Question of Life, the Universe, and Everything is: 42'
END
ELSE
BEGIN
IF (@answer IS NULL)
PRINT 'NULL'
ELSE PRINT CONVERT(char(20), @answer)
END
Will print:
|
Answer to the Ultimate Question of Life, the Universe, and Everything is: 42
2147251896
|
Which means that if the query will return zero results , the variable will keep its old value (and not NULL as we expect). In case of multiple results the value that will be placed into the variable is the value which returned last from the query (2147251896 in my case)
For the second syntax:
--ZERO RESULTS
DECLARE @answer int
SET @answer = 42
SET @answer = (SELECT object_id FROM sys.objects WHERE 1=0)
IF (@answer = 42)
BEGIN
PRINT 'Answer to the Ultimate Question of Life, the Universe, and Everything is: 42'
END
ELSE
BEGIN
IF (@answer IS NULL)
PRINT 'NULL'
ELSE PRINT CONVERT(char(20), @answer)
END
--MUTLIPLE RESULTS
SET @answer = 42
SET @answer = (SELECT object_id FROM sys.objects)
IF (@answer = 42)
BEGIN
PRINT 'Answer to the Ultimate Question of Life, the Universe, and Everything is: 42'
END
ELSE
BEGIN
IF (@answer IS NULL)
PRINT 'NULL'
ELSE PRINT CONVERT(char(20), @answer)
END
Will print:
|
NULL
Msg 512, Level 16, State 1, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Answer to the Ultimate Question of Life, the Universe, and Everything is: 42
|
As you can see in case of zero results NULL will be placed into the variable, and in case of multiple results an error message will appear (but the script continues to run) and the variable will keep the value it had before the query.
These behaviors should be taken in consideration when you write scripts, I used the first syntax in one of my scripts and because I wasn’t aware to the above, it caused me to debug until midnight…