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: Optional Parameters Revisited

  • Comments 6
  • Likes

Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored procedure (the earlier posts are here, here, and here).  The question had basically boiled down to whether there was single-statement syntax available which would preclude the necessity to use dynamic SQL to resolve this requirement.

When we last checked in on this topic in April, I had nominated this syntax as a resolution for this issue:

FROM    dbo.
WHERE   y1 = ISNULL(@p1, y1
     y2 = ISNULL(@p2, y2
     y3 = ISNULL(@p3, y3
     y4 = ISNULL(@p4, y4)

Well, as Adam Machanic pointed out, this syntax fails if ANSI_NULLS is set to ON (which it should be; if you're working with XML, it has to be).  The results it produces are WRONG.

I'm still looking into solutions to this issue.  I've found one so far which requires use of a magic number, which works for numeric data but not for the general case:

DECLARE @constant   INT

-- set @constant to some value that doesn't occur in your data

SET @constant = -32687


-- now run the new syntax


FROM    dbo.foo

WHERE   ISNULL(y1, @constant) = ISNULL(@p1, ISNULL(y1, @constant))

AND     ISNULL(y2, @constant) = ISNULL(@p2, ISNULL(y2, @constant))

AND     ISNULL(y3, @constant) = ISNULL(@p3, ISNULL(y3, @constant))

AND     ISNULL(y4, @constant) = ISNULL(@p4, ISNULL(y4, @constant))

-- the syntax above returns correct results and is easier to code than dynamic sql

--  its only downfall is that it requires a magic number

I'm still looking for datatype-agnostic syntax; although one could code this up with a @constant of each datatype (or one for each column if need be), that approach just multiplies the "magic number" issue.

I'll let you know what I find..


  • What about using the COALESCE function?  This is how I normally would use it:

    WHERE   COALESCE(y1, @constant) = COALESCE(@p1, y1, @constant)

    Disclaimer:  I'm a developer, so there may be unknown SQL Server performance issues, but it seems more readable to me.

  • enderC, the COALESCE is definitely more readable than what I've proposed, and it should have the same performance characteristics.  So good on you!

    I'm still in the market for a solution that's free of magic numbers, though..

  • PingBack from http://blogs.technet.com/wardpond/archive/2006/06/30/439699.aspx

  • Although the solution looks tidy, performance will suck; the only way to deal with optional parameters with SQL Server is still dynamic SQL unfortunetly.

    Because you are using ISNULL or COALESCE in this way will negate any possible index seek operation and will give an effective table scan.


  • PingBack from http://blogs.technet.com/wardpond/archive/2006/07/03/440035.aspx

  • 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