CODEWITHSUNDEEP
sql-server-2008nullisnull
Buzz
Buzz

Reputation: 6330

sql server null value in where clause

There is one table which is having only one row with 4 date columns ,initially all date value are null so 

if Exists(select 1 from rep_master where pacdt_1=null OR
pacdt_2=null OR
pacdt_3=null OR
pacdt_4=null)
select 0
else
select 1

this one is returning 1

if Exists(select 1 from rep_master where ISNULL(pacdt_1,0)=0 or
ISNULL(pacdt_2,0)=0  or
ISNULL(pacdt_3,0)=0 or
ISNULL(pacdt_4,0)=0 )
select 0
else
select 1

this one is returning 0 ,which is correct result
I m unable to figure out what is wrong with first query?

Upvotes: 1

Views: 2309

Answers (2)

Philipi Willemann
Philipi Willemann

Reputation: 658

You can't equal null, you have to use IS

if Exists(select 1 from rep_master where pacdt_1 IS null OR
pacdt_2 IS NULL OR
pacdt_3 IS NULL OR
pacdt_4 IS NULL)
select 0
else
select 1

to get where the value is not null you use IS NOT

if Exists(select 1 from rep_master where pacdt_1 IS NOT null OR
pacdt_2 IS NOT NULL OR
pacdt_3 IS NOT NULL OR
pacdt_4 IS NOT NULL)
select 0
else
select 1

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300759

use IS NULL rather than = NULL

so:

if Exists(select 1 from rep_master 
          where pacdt_1 is null OR pacdt_2 is null OR
                pacdt_3 is null OR pacdt_4 is null)
   select 0
else
   select 1

Upvotes: 3

Related Questions