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
MyColumn
IS NULL
IS NOT NULL
ANY VALUE (NULL AND NOT NULL)
WHERE
IF @parameter BEGIN ... END
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