none
Handling of Null in Case Statement

    General discussion

  • Hello Everybody,
     
    Recently I faced an issue with NULL in CASE statement. I used to write case statement in following manner.
    CASE Variable_Name
        WHEN Value1 THEN Action1
        ELSE DefaultAction
    END
     
     
    but this does not work in case of NULL comparison, for example
    CASE Variable_Name
        WHEN NULL THEN Action1
        ELSE DefaultAction
    END
    will always return DefaultAction, even when Variable_Name have NULL value.
     
     
    Changing this condition in following manner makes it working even in case of NULL
    CASE 
        WHEN Variable_Name IS NULL THEN Action1
        ELSE DefaultAction
    END
     
     
    I ended up with a bug because of this :-(, so find it useful to share with others to avoid similar mistakes.
    I also checked this in Sybase, Oracle and Sql Server. All works in same way for this.
     
    Cheers! Happy coding !!

    Anuj
    Thursday, November 04, 2010 10:10 AM

All replies