Sql Where statement dynamic with case

Question

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?

Answer 1

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)

Answer 2

Assuming I'm ready your question correctly, this should do the trick:

SELECT * FROM yourTable
WHERE (@whereType <> 1 OR Duration > 0)
AND   (@whereType <> 2 OR Duration = 0)

but be careful with this sort of construct if there table contains any significant number of rows: you will be forcing a table scan of yourTable unless you have other filtering clauses for which there are usable indexes.

Edit: Some will find Justin's answer clearer to read (the two acheive the same result), though I prefer to keep the OR clauses inside the brackets as I find the logic easier to follow especially when other filtering clauses are added. In either case the warning about index use and scanning holds.

For Large Tables

Neither this or the other arrangement is suitable for large tables (as your comments suggests you have noticed) unless there are other filtering clauses that dramatically limit the amount of rows the dynamic filter acts upon.

Within a stored procedure you have the option of ad-hoc SQL via EXEC:

SET @SQL = 'SELECT * FROM yourTable '
IF @whereType = 1 THEN @SQL = @SQL + 'WHERE Duration > 0'
IF @whereType = 2 THEN @SQL = @SQL + 'WHERE Duration = 0'
EXEC (@SQL)

Be very careful with this pattern. You can easily open yourself to injection attacks (though this example doesn't) with ad-hoc SQL.

See the article mentioned in Andy's answer for a very detailed discussion of the options available and the issues (with regard to performance, maintainability, and security) you might encounter with each. Erland's other articles are very useful, thorough, and well written too, see http://www.sommarskog.se/index.html (the first one, regarding dynamic SQL, is relevant here too).

Answer 3

See the article "Dynamic Search Conditions in T‑SQL" from Erland Sommarskog at http://www.sommarskog.se/dyn-search.html

This explains in detail your options for dynamic searching and the performance implications of doing so.