PRB: Assignment of NULL Value to Local Variable in SQL (63212)

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.

WORKAROUND

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:

Place the test values of "a" and "b" in the table. For example: a=52, and b=NULL.

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