How to use CASE in a Where clause

Question

Hello,

I have the following script situation:

SELECT *
FROM Formulario 
WHERE DataInclusao = '2019-10-28 08:19:54.000' 
AND chave = 'XXXXXXX' AND Id = 2

This table has a column called Productoid, in which if this query returns the information of Productoid other than null, it must be included this condition in the query. Otherwise, the script remains with the above conditions.

I tried to do it in the following ways

SELECT *
FROM Formulario 
WHERE DataInclusao = '2019-10-28 08:19:54.000' 
AND chave = 'XXXXXXX' AND Id = 2
AND ProductId = (CASE WHEN ProductId IS NULL THEN IS NULL ELSE 150 END)

But in that case, you don’t return anything AND ProductId = NULL and yes AND ProdutoId IS NULL

I tried using IF after all WHERE and nothing worked;

Answer 1

I would make that condition using OR instead of CASE WHEN.

Would look like this:

SELECT *
FROM Formulario 
WHERE DataInclusao = '2019-10-28 08:19:54.000' 
AND chave = 'XXXXXXX' AND Id = 2
AND (ProductId IS NULL OR ProductId = 150)

But if you want to do it with CASE WHEN, would only have to treat null values in this way:

SELECT *
FROM Formulario 
WHERE DataInclusao = '2019-10-28 08:19:54.000' 
AND chave = 'XXXXXXX' AND Id = 2
AND ISNULL(ProductId, 0) = (CASE WHEN ProductId IS NULL THEN 0 ELSE 150 END)

Or you could still use the clause IN:

SELECT *
FROM Formulario 
WHERE DataInclusao = '2019-10-28 08:19:54.000' 
AND chave = 'XXXXXXX' AND Id = 2
AND ISNULL(ProductId, 0) IN (0, 150)