You are checking a variable for equality with a literal value without having
yet assigned a value. Also, with ANSI_NULLS on, you cannot use comparison type
operators to check a variable for NULL as '= NULL' and '<> NULL' will be
'unknown' when NULL values are involved. You must use 'is null' or 'is not
null' instead (or functions like IsNull, NullIf, Coalesce). Of course you can
use comparison type operators to compare non-NULL values.
A bit variable can be NULL, or assigned the value NULL (unknown), 0 or 1 by a
simple assignment or a complex select statement. Therefore, to test all
possible cases try something like:
declare @var bit
-- a complex select statement or a simple assignment
set @var = NULL -- try all of NULL, 0 or 1 or comment out the whole line
if @var is not null
if @var <> 1
print 'value is not equal to 1'
else
print 'value is 1'
else
print 'value is unknown'
Hope that makes sense.
Cheers,
Chris
From: [email protected] [mailto:[email protected]] On
Behalf Of Wallace Turner
Sent: Wednesday, 18 January 2012 6:20 PM
To: ozDotNet
Subject: [OT] t-sql, dealing with null
Consider:
declare @var bit
if (select @var) <> 1
print 'value is not equal to 1'
else
print 'value is 1'
assuming ANSI_NULLS is on then the output is:
value is 1
What are some common ways that people deal with this 'gotcha' ? eg
1) don't use <> operator at all
2) use ISNULL
Please consider the case where the select is a (more) complex statement.
Regards
Wal
