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