CODEWITHSUNDEEP
sql-servernull
Phillip Senn
Phillip Senn

Reputation: 47595

Why am I getting not null here?

I'm getting 'not null' followed by 'null':

DECLARE @X Int

SELECT CASE @X
    WHEN NULL THEN 'NULL'
    ELSE 'NOT NULL'
    END

SELECT 'NULL'
WHERE @X IS NULL

Upvotes: 2

Views: 51

Answers (2)

mindbdev
mindbdev

Reputation: 444

Use

SET ANSI_NULLS OFF

before your query . Sql server by default does not support equating expressions with null

by default it comes with 

SET ANSI_NULLS ON

while ANSI_NULLS is on you cannot use NULL = NULL Checking instead "IS NULL" has to be used

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Because NULL <> NULL

In case statement you comparing NULL = NULL it failed so NOT NULL got selected.

Try changing you Case statement like this.

SELECT CASE 
         WHEN @X IS NULL THEN 'NULL'
         ELSE 'NOT NULL'
       END

Upvotes: 4

Related Questions