1

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?

3 Answers 3

5

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)
1
  • Do note that this will cause a table scan if there are no other filtering/joining clauses. See other answers for alternatives using ad-hoc SQL. Oct 24, 2016 at 8:19
3

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).

1
  • the table is very large, over 100gb per day. using this and the blow example is not allowing the results to come back. any other options? Oct 21, 2016 at 15:54
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.