CASE WHEN Problem with CASE NULL

Question

Hi All,

I am having problems with the following SELECT CASE statement when trying to handle NULL values.  In the following select

SELECT     

st3.description 

, CASE st3.description WHEN NULL THEN 'I am Null' ELSE 'This is else' END AS Expr2

, ISNULL(st3.description, 'Null Value') AS Expr3

FROM  structure AS st3 

When st3.description is NULL I want EXPR2 to display 'I AM NULL' but 'WHEN NULL' does not seem to work.  Column 'description' is of type VARCHAR(50).

This is the results I get are as follows:-

description Expr2 Expr3

NULL This is else Null Value

NULL This is else Null Value

Amber This is else Amber

Amber This is else Amber

How do I pick up NULL values in a 'case when' statement.

TIA

Andi

Answer 1

Here is the proper syntax for cheking a NULL value in a CASE statment.

 

Declare @test int

Select Case When @test IS NULL THEN 'Value is Null' ELSE 'Value is not Null' END Test

 

Answer 2

Thanks for that Ken,

How do I use that from within my query.

When I try:- 

SELECT     

st3.description 

, CASE st3.description WHEN IS NULL THEN 'I am Null' ELSE 'I am NOT Null' END Expr2

, ISNULL(st3.description, 'Null Value') AS Expr3

FROM  structure AS st3 

I get the error - Incorrect syntax near the keyword 'IS'.

Answer 3

Try:

 

Code Snippet

SELECT

st3.description

, CASE WHEN st3.description IS NULL THEN 'I am Null' ELSE 'I am NOT Null' END Expr2

, ISNULL(st3.description, 'Null Value') AS Expr3

FROM structure AS st3

 

 

HTH!

Answer 4

remove the st3.description  after case.  Its CASE WHEN if you do expression check with null

Answer 5

Sorry - I've cracked it:-

SELECT     

st3.description 

, CASE st3.description WHEN   NULL THEN 'I am Null' ELSE 'I am NOT Null' END Expr2

, CASE When st3.description is null  THEN 'Value is Null' WHEN st3.description  = '.NET' Then 'Value is .NET' ELSE 'Value is not Null' END Test

, ISNULL(st3.description, 'Null Value') AS Expr3

FROM  structure AS st3 

Thanks

Answer 6

Hi Vladimir,

 

The CASE statement has 2 forms which need to be understood.

 

Form 1

CASE scalar_expression1

  WHEN scalar_expression2 THEN ...

  WHEN scalar_expression3 THEN ...

  ELSE ...

END

 

In this form, scalar_expression1 is comapred sequntially using = with each WHEN clause and the first match is executed. However NULL = NULL if false and hence you can't use this form in your SQL

 

Form 2

CASE

  WHEN boolean_expression1 THEN ...

  WHEN boolean_expression2 THEN ...

  ELSE ...

END

 

In this form each boolean_expression is evaluated sequentially and the first to return true is executed. Here you can use the IS operator that is used to test for NULL values. e.g. WHEN column IS NULL THEN.

 

So in your case you want to use:

SELECT st3.description 

, CASE WHEN st3.description IS NULL THEN 'I am Null' ELSE 'I am NOT Null' END Expr2

, ISNULL(st3.description, 'Null Value') AS Expr3

FROM  structure AS st3 

Answer 7

Thanks for all your help ;-)

Answer 8

Hi Adam

There is a way to test for NULL in the scalar form of the CASE Statement above. This works for me:

CASE ISNULL(scalar_expression1,'NULL')

  WHEN scalar_expression2 THEN ...

  WHEN scalar_expression3 THEN ...

  WHEN 'NULL' THEN ...

  ELSE ...

END

As long as 'NULL' is not a valid value in the column you are testing, this should work.

Thanks - Adam.

---

Adam Gilmore - Dimodelo Solutions - dimodelo.com

Answer 9

NULL does not equal anything.  The case statement is basically saying when the value = NULL .. it will never hit.  
***There are also several system stored procedures that are written incorrectly with your syntax.  See sp_addpullsubscription_agent and sp_who2.