SQL SERVER - CASE in WHERE (THEN IS NULL ELSE IS NOT NULL )

Question

I have seen this link to find a solution to my problem:

However, in my case I need to evaluate that when a parameter has a certain value, it evaluates a field when it is null or not, for example:

WHERE (CASE @parameter WHEN 1 THEN a.fieldOne IS NULL ELSE a.fieldOne IS NOT NULL END)

But this doesn't work for me...

EDIT 1

For example when @parameter = 1

SELECT * FROM
FROM exampleTable as s
WHERE (s.fieldOne IS NULL)

And when @parameter = 0

SELECT * FROM
FROM exampleTable as s
WHERE (s.fieldOne IS NOT NULL)

Could you explain "doesn't work"? What are you trying to achieve with `ELSE a.fieldOne IS NOT NULL`. A `CASE` expression returns a scalar value. — Thom A, Sep 11 '18 at 18:10
What output are you trying to get? `fieldOne IS NULL` is a conditional test, not an output value. — Tab Alleman, Sep 11 '18 at 18:11
Probably duplicate: https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server — Tab Alleman, Sep 11 '18 at 18:13

score 3 · Accepted Answer · answered Sep 11 '18 at 18:12

3

You can't use a case expression to return parts of a query like you tried, only values.

However, you could emulate his behavior with logical opertaors:

WHERE (@parameter =  1 AND a.fieldOne IS NULL) OR
      (@parameter <> 1 AND a.fieldOne IS NOT NULL)

answered Sep 11 '18 at 18:12

Mureinik

1 Answers1