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

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?

I've had success with this solution. It's almost like Patrick's, with a little twist. You can use these expressions separately or in sequence. If the parameter is blank, it will be ignored and all values for the column that your searching will be displayed, including NULLS.

SELECT * FROM MyTable
WHERE 
    --check to see if @param1 exists, if @param1 is blank, return all
    --records excluding filters below
(Col1 LIKE '%' + @param1 + '%' OR @param1 = '')
AND
    --where you want to search multiple columns using the same parameter
    --enclose the first 'OR' expression in braces and enclose the entire 
    --expression 
((Col2 LIKE '%' + @searchString + '%' OR Col3 LIKE '%' + @searchString + '%') OR @searchString = '')
AND
    --if your search requires a date you could do the following
(Cast(DateCol AS DATE) BETWEEN CAST(@dateParam AS Date) AND CAST(GETDATE() AS DATE) OR @dateParam = '')

This kind of logic could be implemented using EXISTS:

CREATE TABLE tab(a INT, b VARCHAR(10));
INSERT INTO tab(a,b) VALUES(1,'a'),(1, NULL),(NULL, 'a'),(2,'b');

Query:

DECLARE @a INT;

--SET @a = 1;    -- specific NOT NULL value
--SET @a = NULL; -- NULL value
--SET @a = -1;   -- all values

SELECT *
FROM tab t
WHERE EXISTS(SELECT t.a INTERSECT SELECT @a UNION SELECT @a WHERE @a = '-1');

db<>fiddle demo

It could be extended to contain multiple params:

SELECT *
FROM tab t
WHERE EXISTS(SELECT t.a INTERSECT SELECT @a UNION SELECT @a WHERE @a = '-1')
  AND EXISTS(SELECT t.b INTERSECT SELECT @b UNION SELECT @a WHERE @b = '-1');

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.

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.

Reference: COALESCE (Transact-SQL).

An other way of CASE:

SELECT *  
FROM MyTable
WHERE 1 = CASE WHEN @myParm = value1 AND MyColumn IS NULL     THEN 1 
               WHEN @myParm = value2 AND MyColumn IS NOT NULL THEN 1 
               WHEN @myParm = value3                          THEN 1 
          END

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;