WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Question

I have a stored procedure in SQL Server 2000 that performs a search based on parameter values. For one of the parameters passed in, I need a different

WHERE

clause depending on its value - the problem is that the 3 values would be where

MyColumn

IS NULL

IS NOT NULL

ANY VALUE (NULL AND NOT NULL)
(essentially no
WHERE
clause)

I'm having some mental block in coming up with the correct syntax. Is this possible to do in one select statement without performing some

IF @parameter BEGIN ... END

branching?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Here is how you can solve this using a single WHERE clause:

WHERE (@myParm = value1 AND MyColumn IS NULL)
OR  (@myParm = value2 AND MyColumn IS NOT NULL)
OR  (@myParm = value3)

A naïve usage of the CASE statement does not work, by this I mean the following:

SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
    WHEN value1 THEN MyColumn IS NULL
    WHEN value2 THEN MyColumn IS NOT NULL
    WHEN value3 THEN TRUE
END

It is possible to solve this using a case statement, see onedaywhen's answer

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Sign up