CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Abhishek Iyer
Abhishek Iyer

Reputation: 614

How do I use an if statement inside the where clause in this sql statement?

 select * 
 from mytable
 where (if @key=0 
           pkey>=0
        else
           pkey = @key)

@key is the value passed in to the stored procedure and pkey is a column in mytable.

Upvotes: 0

Views: 1727

Answers (3)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

This will work for you . by using case statement you can apply dynamic filter ..

select *   
from mytable  
where  1 = case 
             when @key=0 then case when pkey>=0 then 1 else 0 end
             else case when pkey = @key then 1 else 0 end
           end

Upvotes: 0

Kaf
Kaf

Reputation: 33809

You use CASE (bit like the way you are trying with if) as below. (DEMO)

select * 
from mytable
where pkey = case when @key <> 0 then @key 
                  else Abs(pkey) end

Upvotes: 1

E.T.
E.T.

Reputation: 944

How about this:

 select * 
 from mytable
 where ((@key=0 AND pkey>=0) OR (@key<>0 AND pkey = @key))

Upvotes: 2

Related Questions