CODEWITHSUNDEEP
sqlsql-servercasewhere-clause
CyborgNinja23
CyborgNinja23

Reputation: 289

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

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

CASE in WHERE, SQL Server

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)

Upvotes: 0

Views: 727

Answers (1)

Mureinik
Mureinik

Reputation: 312259

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)

Upvotes: 3

Related Questions