CODEWITHSUNDEEP
sql-server-2005
jay
jay

Reputation: 3899

parameter to check is null or is not null in sql server 2008?

I have one table Employee

       Eno     ename   image

       1        aaa     a.jpg
       2        bbb     b.jpg
       3        ccc     null
       4        ddd     null

I pass the parameter to the query 

   declare @a varchar(10)

   select * from employee where ?

? if i pass image is not null mean i want all employee details who have image other wise

i want image is null employee details who not have image.

Upvotes: 1

Views: 4082

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

This is short and sweet but not SARGable so it won't perform well:

select *
    from employee
    where coalesce(image,'') = coalesce(@a,'')

For better performance, I'd go with:

if @a is not null
    select *
        from employee
        where image = @a
else
    select *
        from employee
        where image is null

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

Not 100% sure but I think you want

WHERE     (@a is null AND image is NULL)
   OR (@a is not null AND image is not NULL)

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

select * 
from employee 
where (@a is null and image is null)
    or (@a is not null and image is not null )

Upvotes: 1

Related Questions