PRB: Assignment of NULL Value to Local Variable in SQL

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

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

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:

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