다음을 통해 공유

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

Comments

  • Anonymous
    January 01, 2003
    PingBack from http://blogs.technet.com/wardpond/archive/2006/06/30/439699.aspx
  • Anonymous
    January 01, 2003
    PingBack from http://blogs.technet.com/wardpond/archive/2006/07/03/440035.aspx
  • Anonymous
    January 01, 2003
    Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...
  • Anonymous
    June 30, 2006
    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.
  • Anonymous
    June 30, 2006
    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..
  • Anonymous
    July 03, 2006
    The comment has been removed