CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
DavidWaldo
DavidWaldo

Reputation: 735

Need to assign null into condition by CASE

I need help with condition for my query.

I want it to work like this:

WHERE 
    [Column] = CASE
                  WHEN @Variable1 IN (1,2) 
                     THEN NULL
                     ELSE @Variable2
               END

But it won't work because [Column] = NULL is not working on our work sever, SQL Server 2012.

Any ideas?

Upvotes: 2

Views: 118

Answers (3)

Dmitry
Dmitry

Reputation: 518

Try this:

WHERE ISNULL([Column],1) = CASE
                WHEN @Variable1 IN (1,2) THEN  1
                ELSE @Variable2
             END

P.S. Change value 1 to any value that not valid for [Column].

Upvotes: 1

HABO
HABO

Reputation: 15816

WHERE CASE
  WHEN @Variable1 IN (1,2) AND [Column] IS NULL THEN 1
  WHEN [Column] = @Variable2 THEN 1
  ELSE 0 END = 1

Upvotes: 0

fen1x
fen1x

Reputation: 5880

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

You have to use IS NULL or IS NOT NULL operators. Rewrite your condition:

WHERE ([Column] IS NULL AND @Variable1 IN (1,2) 
   OR [Column] = @Variable2)
   AND ...other conditions...

Upvotes: 0

Related Questions