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: An Incrementally Better Mousetrap For Optional Parameters

  • Comments 4
  • Likes

enderC points out that the syntax I proposed in last night's post on optional parameters can be made more readable thusly..


FROM    dbo.foo

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

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

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

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

enderC's suggestion is spot-on.  If the best we can do is a "magic number" implementation, I don't think it's going to get much better than this.

I'm still in the market for a datatype-agnostic, non-magic-number solution to this requirement that performs and scales.  If you've got one, bring it on!

We now return you to my regularly scheduled vacation..


  • Ward,

    Unfortunately, that syntax will force a table scan.  You need to not use COALESCE (or any other function) on the columns used in the sarg... IMHO, dynamic SQL is the only way to go here.

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

  • When I first published Pond’s Laws, I promised it would be a living document.  Herewith is the first evidence.

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