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:

FROM    dbo.
WHERE   y1 = ISNULL(@p1, y1
     y2 = ISNULL(@p2, y2
     y3 = ISNULL(@p3, y3
     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



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..


Comments (6)

  1. Anonymous says:

    Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely…

  2. enderC says:

    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.

  3. Ward Pond says:

    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..

  4. Tony Rogerson says:

    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.


Skip to main content