Dynamic Search Conditions with T-SQL

Long article but great one by Erland Sommarskog on searching data with many criterias.

I prefer myself  stored procedure, but there you can find some good alternatives.

 

 

 

2 Comments

  • I used to use dynamic sql in a sp for this until I found coalese. This can be used in the where as follows (using his vars)- ... where orderid = coalese(@orderid, orderid) and orderdate >= coalesce(orderdate, @orderdate) ... etc. So if the variable is null, it will just evaluate to orderid = orderid

  • As mentioned in the article, the coalesce approach doesn't work well if the field is nullable and contains null values - those rows don't get returned. Coalesce also usually causes a table scan which is bad on performance. Found this out the hard way.



    We switched ours to use the "name = @name or @name is null" approach. This returns correct data for nullable columns and also usually at least gets you an index scan which should make performance acceptable as long as it's not a giant table.

Comments have been disabled for this content.