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.

Skip to main content