ID Number: Q63212
1.10 1.11 4.20
OS/2
Summary:
SYMPTOMS
Suppose a table named "test" has columns named "a" and "b", where
the highest value of "a" is 2. "@a" is assigned a value in the
normal way, as follows:
declare @a int
select @a=100
select @a=b from test where a=52
select @a
It is expected that this query will return NULL because "a" is
never 52. However, the query returns 100, which is the number that
was first assigned to it.
If the third line of the above query is replaced with the following
line, NULL is returned as expected:
select @a=(select b from test where a=52)
CAUSE
In the example listed above, the SELECT statement returns zero
rows. Because there is no value of "b" returned from the query, SQL
Server treats the previous value of @a (100) as the "default value"
to use. This is the reason it does not return NULL as expected.
RESOLUTION
This question raises the issue of the different ways SQL Server
treats NULL expressions.
To answer the question of why the assignment of a value to a local
variable works differently when the value is NULL, the following
test was performed using the example listed above:
1. Place the test values of "a" and "b" in the table. For example:
a=52, and b=NULL.
2. Set @a = 100, and perform the following select statement:
select @a=b from test where a=52
The variable @a is successfully assigned as NULL.
The difference between this test and the test listed above is the
"value" of "b" returned from the query. The second SELECT statement
is needed so that the return value of the "entire expression" can
be obtained, which is NULL, rather than the return value of "b".
Additional reference words: 1.10 1.11 4.20 Transact-SQL