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

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

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.

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;