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