locked
CASE WHEN Problem with CASE NULL RRS feed

  • 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

    Monday, September 8, 2008 12:28 AM

Answers

  • 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

     

    Monday, September 8, 2008 1:34 AM
  • 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 
    Monday, September 8, 2008 4:22 PM

All replies

  • 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

     

    Monday, September 8, 2008 1:34 AM
  • 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'.


    Monday, September 8, 2008 4:08 PM
  • 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!

    Monday, September 8, 2008 4:14 PM
  • remove the st3.description  after case.  Its CASE WHEN if you do expression check with null

    Monday, September 8, 2008 4:15 PM
  • 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


    Monday, September 8, 2008 4:15 PM
  • 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 
    Monday, September 8, 2008 4:22 PM
  • Thanks for all your help ;-)
    Monday, September 8, 2008 6:13 PM
  • 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

    Monday, July 1, 2013 2:14 AM
  • 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.  
    Thursday, March 8, 2018 8:32 PM