A colleague asked the following question recently:

I have a stored procedure that takes four parameters that are optional.  My WHERE clause is supposed to include those parameters that are NOT NULL.  If, for example, only @p1 and @p3 have a value, the query will be:

SELECT x
FROM   dbo.foo
WHERE  y1 = @p1
AND    y3 = @p3

I have a few ideas on how to generate the WHERE clause dynamically; however, they are very messy.  I wonder if there is a more elegent way to generate the WHERE clause.

I offered the following syntax to my colleague, who later described it as a perfect solution to his issue, which is why I'm posting it here. :-)

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)

With this syntax, if any of the parameters are NULL, the column will be compared to itself, logically falling out of the query.  Note that this approach is not without performance implications, but it's a far more maintainable syntax than building dynamic SQL, which is what most other approaches I've seen to this issue imply.  In this instance, you'd also want to make sure you had an index on dbo.foo(y1, y2, y3, y4).

I hope this little tidbit comes in handy for you!

     -wp