Microsoft KB Archive/63212

= PRB: Assignment of NULL Value to Local Variable in SQL =

Article ID: 63212

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q63212



SYMPTOMS
Suppose a table named &quot;test&quot; has columns named &quot;a&quot; and &quot;b&quot;, where the highest value of &quot;a&quot; is 2. &quot;@a&quot; 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 &quot;a&quot; 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 &quot;b&quot; returned from the query, SQL Server treats the previous value of @a (100) as the &quot;default value&quot; 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 &quot;a&quot; and &quot;b&quot; 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 &quot;value&quot; of &quot;b&quot; returned from the query. The second SELECT statement is needed so that the return value of the &quot;entire expression&quot; can be obtained, which is NULL, rather than the return value of &quot;b&quot;.

Additional query words: Transact-SQL Windows NT

Keywords: kbother KB63212

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.