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 (4)

  1. Anonymous says:

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

  2. Anonymous says:

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

  3. Adam Machanic says:

    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.