PRSQL9204009: Nulls Not Returned When Using Local Variables
PSS ID Number: Q69992 Article last modified on 12-18-1992
1.10 1.11 OS/2
Summary: PROBLEM ID: PRSQL9204009
SYMPTOMS Using a local variable to test for null values against a character field does not return the expected results. Testing for null values against integer fields, however, does return the correct results.
STATUS Microsoft has confirmed this to be a problem in SQL Server versions 1.1 and 1.11. This problem was corrected in SQL Server version 4.2.
More Information: The following example uses integer fields, and works as expected. First, the test table is created with an integer field: drop table test go create table test(a int null) go insert test values(1) insert test values(null) go Next, the local variable, “@x”, is set to null and is used to test the column “a” for null values. Note that both SELECT statements return the expected results: declare @x int select @x=null select * from test where a=@x select * from test where a=null go a ———– NULL (1 row affected) a ———– NULL (1 row affected) In contrast, the following example uses character fields and does not return expected results. First, the test table is created with a character field: drop table test go create table test(a char(1) null) go insert test values(‘1’) insert test values(null) go Now when the local variable, “@x”, is set to null and is used to test the column “a” for null values, zero rows are returned. The expected results are once again illustrated by the second SELECT statement, which returns one row: declare @x char(1) select @x=null select * from test where a=@x select * from test where a=null go a - (0 rows affected) a - NULL (1 row affected)
Additional reference words: Transact-SQL
Copyright Microsoft Corporation 1992.
