Database Programming: An Incrementally Better Mousetrap For Optional Parameters

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

SELECT x

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

-wp

Comments

• Anonymous
  January 01, 2003
  PingBack from http://blogs.technet.com/wardpond/archive/2006/07/03/440035.aspx
• Anonymous
  January 01, 2003
  When I first published Pond’s Laws, I promised it would be a living document.  Herewith is the first evidence.
• Anonymous
  January 01, 2003
  Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...
• Anonymous
  July 01, 2006
  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.