Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored procedure (the earlier posts are here, here, and here). The question had basically boiled down to whether there was single-statement syntax available which would preclude the necessity to use dynamic SQL to resolve this requirement.
When we last checked in on this topic in April, I had nominated this syntax as a resolution for this issue:
Well, as Adam Machanic pointed out, this syntax fails if ANSI_NULLS is set to ON (which it should be; if you're working with XML, it has to be). The results it produces are WRONG.
I'm still looking into solutions to this issue. I've found one so far which requires use of a magic number, which works for numeric data but not for the general case:
DECLARE @constant INT
-- set @constant to some value that doesn't occur in your data
SET @constant = -32687
-- now run the new syntax
WHERE ISNULL(y1, @constant) = ISNULL(@p1, ISNULL(y1, @constant))
AND ISNULL(y2, @constant) = ISNULL(@p2, ISNULL(y2, @constant))
AND ISNULL(y3, @constant) = ISNULL(@p3, ISNULL(y3, @constant))
AND ISNULL(y4, @constant) = ISNULL(@p4, ISNULL(y4, @constant))
-- the syntax above returns correct results and is easier to code than dynamic sql
-- its only downfall is that it requires a magic number
I'm still looking for datatype-agnostic syntax; although one could code this up with a @constant of each datatype (or one for each column if need be), that approach just multiplies the "magic number" issue.
I'll let you know what I find..
What about using the COALESCE function? This is how I normally would use it:
WHERE COALESCE(y1, @constant) = COALESCE(@p1, y1, @constant)
Disclaimer: I'm a developer, so there may be unknown SQL Server performance issues, but it seems more readable to me.
enderC, the COALESCE is definitely more readable than what I've proposed, and it should have the same performance characteristics. So good on you!
I'm still in the market for a solution that's free of magic numbers, though..
PingBack from http://blogs.technet.com/wardpond/archive/2006/06/30/439699.aspx
Although the solution looks tidy, performance will suck; the only way to deal with optional parameters with SQL Server is still dynamic SQL unfortunetly.
Because you are using ISNULL or COALESCE in this way will negate any possible index seek operation and will give an effective table scan.
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...