Sql-server – Sql Where statement dynamic with case

sql servert-sql

I have a Stored Procedure where I pass a value of 0,1,2.
0= no where
1= Duration >0
2= Duration =0

select * from Table where case when @duration =1 then Duration else 1
end >0

this works however i can't get the other values to work. I've tried 3 case statements but only the last one works.

How can I get a dynamic where statement to work for 3 values?

Best Answer

I wouldn't use a case statement. Just use boolean logic 

select *
  from table
 where (@duration = 0)
    or (@duration = 1 and duration > 0)
    or (@duration = 2 and duration = 0)
Related Question