Database Programming: Implementing Optional Parameters To A Stored Procedure
A colleague asked the following question recently:
I have a stored procedure that takes four parameters that are optional. My WHERE clause is supposed to include those parameters that are NOT NULL. If, for example, only @p1 and @p3 have a value, the query will be:
SELECT
x
FROM dbo.foo
WHERE y1 = @p1
AND y3 = @p3I have a few ideas on how to generate the WHERE clause dynamically; however, they are very messy. I wonder if there is a more elegent way to generate the WHERE clause.
I offered the following syntax to my colleague, who later described it as a perfect solution to his issue, which is why I'm posting it here. :-)
SELECT x
FROM dbo.foo
WHERE y1 = ISNULL(@p1, y1)
AND y2 = ISNULL(@p2, y2)
AND y3 = ISNULL(@p3, y3)
AND y4 = ISNULL(@p4, y4)
With this syntax, if any of the parameters are NULL, the column will be compared to itself, logically falling out of the query. Note that this approach is not without performance implications, but it's a far more maintainable syntax than building dynamic SQL, which is what most other approaches I've seen to this issue imply. In this instance, you'd also want to make sure you had an index on dbo.foo(y1, y2, y3, y4).
I hope this little tidbit comes in handy for you!
-wp
Comments
- Anonymous
January 01, 2003
Another entry in the "optional parameters" thread - Anonymous
January 01, 2003
The performance implications of a recently discussed technique for passing optional parameters to a stored procedure are clarified. - Anonymous
January 01, 2003
Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored... - Anonymous
January 01, 2003
Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely... - Anonymous
April 17, 2006
Could you elaborate on the performance implications? I'm doing something similar and wonder about that... - Anonymous
April 17, 2006
Hi Brian..
The performance issues are basically around optimization of the query plan.
In the example above, if the cardinality of all combinations of the four parameters is roughly identical (say, in the most obvious case, they're unique) then there should be no performance issues with this construction if you build the recommended index. Each invocation would result in an index seek, which is the best possible outcome in this scenario.
If, however, the cardinality of unique sets of parameters is markedly different (say there is one occurrence of '1,2,3,4' and 100,000 occurrences of '1,2,3,5' among the data), then we might get into trouble if we compile the stored procedure with the '1,2,3,4' parameter set and subsequently run it with the '1,2,3,5' parameter set. In this case, the best approach would most likely be to use the version-appropriate RECOMPILE option. In SQL Server 2000, you'd build the entire stored procedure with the WITH RECOMPILE option. In SQL Server 2005, you'd place the OPTION (RECOMPILE) directive on the SELECT statement.
Please let me know if there's anything further you need. Thanks for your question!
-wp - Anonymous
April 17, 2006
That's really interesting. I haven't studied books-online and only recently discovered IsNull for something else. Until now, I've done something like this:
WHERE ((col = @arg) OR (@arg is null))
AND ...
I'm considering trying
WHERE col = ISNULL(@arg, col)
Instead. But while I'm sure the result would be the same, I wonder if the expense would be. Taking the case where @arg IS NULL it seems like my current clause would be faster because there is no index to worry about.
I guess it all depends on the state of RECOMPILE and how it all ran the first time, eh?
-Brian - Anonymous
April 17, 2006
Hi Brian..
The result and the expense would be exactly the same; the ISNULL syntax is just a more compact representation of your OR construction. Furthermore, NULLs are rendered in indexes, so NULL values in your parameters will not impact the index selected for the query (remember, you should build ONE index on all of your criteria, rather than a single index on each criteria).
The same performance issues and the same caveats around cardinality and index creation would apply to each construction; if your data has dramatically variable cardinality, then you'll need to use the appropriate RECOMPILE hint to maximize your performance.
Hope this helps! - Anonymous
April 17, 2006
Hi Ward,
I recommend that you check out Erland Sommarskog's article on this topic:
http://www.sommarskog.se/dyn-search.html - Anonymous
April 18, 2006
Thanks for the reference, Adam. I'm swamped right now ao I only had a chance to skim the article, but I believe that this behavior is different under SQL2K5.
As soon as I have enough time to complete a proper test, I'll post the results here. Thanks again for pointing me at the Erland Sommarskog article.
-wp