Database Programming: Optional Parameters Revisited
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:
SELECT
x
FROM dbo.foo
WHERE y1 = ISNULL(@p1, y1)
AND y2 = ISNULL(@p2, y2)
AND y3 = ISNULL(@p3, y3)
AND y4 = ISNULL(@p4, y4)
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
SELECT x
FROM dbo.foo
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..
-wp