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

1. IS NULL
2. IS NOT NULL
3. 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?

Answer 1

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

Someone else mentioned the CASE statement, this would go something like:

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

EDIT:

I did a brief search and found this forum post which implies that the CASE statement is not usable in this way, so try it and if it doesn't work the first method may have to do.

Answer 2

You should take a look at the CASE statement.

Answer 3

You could just do something like this:

SELECT *
FROM foo
WHERE (@param = 0 AND MyColumn IS NULL)
OR (@param = 1 AND MyColumn IS NOT NULL)
OR (@param = 2)

Something like that.

Answer 4

COALESCE

WHERE MyColumn = COALESCE(@value,MyColumn)

If @value is NULL, it will compare MyColumn to itself, ignoring @value = no where clause.

IF @value has a value (NOT NULL) it will compare MyColumn to @value

Answer 5

This is how it can be done using CASE:

DECLARE @myParam INT;
SET @myParam = 1;

SELECT * 
  FROM MyTable
 WHERE 'T' = CASE @myParam
             WHEN 1 THEN 
                CASE WHEN MyColumn IS NULL THEN 'T' END
             WHEN 2 THEN
                CASE WHEN MyColumn IS NOT NULL THEN 'T' END
             WHEN 3 THEN 'T' END;