Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: Implementing Optional Parameters To A Stored Procedure

Database Programming: Implementing Optional Parameters To A Stored Procedure

  • Comments 10
  • Likes

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 = @p3

I 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
  • Could you elaborate on the performance implications? I'm doing something similar and wonder about that...

  • 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

  • The performance implications of a recently discussed technique for passing optional parameters to a stored procedure are clarified.

  • 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

  • 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!

  • Hi Ward,

    I recommend that you check out Erland Sommarskog's article on this topic:

    http://www.sommarskog.se/dyn-search.html

  • 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

  • Another entry in the "optional parameters" thread

  • Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...

  • Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment