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?

Grapery

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

Amarillo

You had the same idea as I did. However, you do need the parentheses. – Goddaughter

I'm not sure whether you do or don't need the parentheses, but I'm a big advocate of parentheses in complex boolean expressions. Not having them leaves it up to the compiler to do order-of-operations and is rather scary, if you ask me. – Riemann

I'd already tried the CASE statement, but CASE evaluates to one result expression, therefore evaluating to 'IS NULL', 'IS NOT NULL', 'myColumn IS NULL' etc does not work. Just testing your first answer now – Grapery

I also tried it and CASE doesn't work this way. My suggestion to use the CASE statement to solve this problem (see my answer) is therefore wrong. – Kyphosis

i didn't think about doing it like this. now i need to go and fix some of my stored procedures... thanks – Kittykitwe

Let's say that I need to filter by a string. If the string is null I should return all the records from a table, if it is not null I should return all the records that match the string or part of it (LIKE operator). How would you solve that? – Cultivable

Answer 2

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.

Goddaughter

Let's say that I need to filter by a string. If the string is null I should return all the records from a table, if it is not null I should return all the records that match the string or part of it (LIKE operator). How would you solve that? – Cultivable

Answer 3

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;

Supermundane

Answer 4

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).

Orison

I don't believe you can use the CASE statement in this scenario as CASE evaluates to one result expression. COALESCE or even ISNULL I don't believe will work either. – Grapery

please review my edit, am I completely missing something in your question? – Orison

@Deviant - I think your missing the question slightly. The WHERE clause on MyColumn needs to be set by another value because it does not directly relate to the parameter value i.e. if NULL is passed, then WHERE clause is negated. If any other value is passed, then the resultset will be only for WHERE MyColumn equals that value, not MyColumn having a known value. The WHERE clause of MyColumn having a NULLvalue is not addressed. – Grapery

This is how using COALESCE would work, which is not what I need – Grapery

Let's say that I need to filter by a string. If the string is null I should return all the records from a table, if it is not null I should return all the records that match the string or part of it (LIKE operator). How would you solve that? – Cultivable

Answer 5

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

Perloff

Answer 6

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 = '')

Ative

Answer 7

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');

Bygone