enderC points out that the syntax I proposed in last night's post on optional parameters can be made more readable thusly..
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..
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&nbsp;great "optional parameter code-off" has been completed.&nbsp; You definitely...
When I first published Pond’s Laws, I promised it would be a living document. Herewith is the first evidence.