CODEWITHSUNDEEP
sqlsql-servernullconditional-statements
Renne007
Renne007

Reputation: 1137

SQL Conditional loses NULL values

I am using SQL server 2005. I have a variable @var that can take values 0, 1, or NULL. I care about it being 1 or not, 0 and NULL are the same for me. But when I set it to be not 1, I lose the NULL values:

DECLARE @var INT
SET     @var = NULL

IF @var <> 1
    PRINT 'not 1'
ELSE 
    PRINT 'equals 1'

The output is "equals 1". What would be the correct code to get "not 1"?

Upvotes: 0

Views: 66

Answers (4)

Steve Hood
Steve Hood

Reputation: 88

NULL means unknown, so SQL Server can't say with 100% certainty that it's not 1. On the other hand if you had "IF @val = 1" then it would say that it can't be certain that it is 1 and move to your ELSE statement.

The COALESCE and ISNULL solutions will work for you by converting the NULL value to a known value, but it's important to know why it's working the way it is.

http://technet.microsoft.com/en-us/library/ms191504(v=SQL.105).aspx

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156978

You could also use coalesce for this:

if coalesce(@var, 0) <> 1
    PRINT 'not 1'
ELSE 
    PRINT 'equals 1'

Upvotes: 2

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

You cannot compare to NULL, this is the property that is set by using directive 

SET ANSI-NULLS OFF

More on that can be seen here :

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

However, this flag is deprecated and you shouldn't really use it.

Correct way here would be 

IF @var IS NULL OR @var <> 1
    PRINT 'not 1'
ELSE 
    PRINT 'equals 1'

Upvotes: 0

juergen d
juergen d

Reputation: 204766

IF @var = 1
    PRINT 'equals 1'
ELSE 
    PRINT 'not 1'

or

IF @var <> 1 or @var is null
    PRINT 'not 1'
ELSE 
    PRINT 'equals 1'

since comparing to null results in unknown which is not true. Use the IS operator for null values.

Upvotes: 2

Related Questions