IS NULL problem

  • Consider the following:

    DECLARE @Datum datetime

    --SELECT @Datum = getdate()

    SELECT@Datum AS RealDate,

    CASE @Datum

    WHEN NULL THEN 'NULL'

    ELSE 'NOT NULL'

    END

    AS NULLed

    SELECT @Datum AS RealDate,

    ISNULL(@Datum,NULL) AS NULLed

    I wish to find out if a column has a NULL value and do something based on TRUE or FALSE. The ISNULL function gives a correct result, but the CASE statement does not! If I give @Datum a value, ISNULL works correctly, but CASE just gives me the same value...

    I'm thinking of writing my own fucntion, but I am amazed why the CASE statement does not give me the correct value. Can anybody enlighten me? Any hints?

    Greetz,
    Hans Brouwer

  • Its not the CASE statement but because of the NULL. There are lot of differences between NULL and IS NULL.

    Here are some of nice articles about the differences...

    http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

    http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

    DECLARE @Datum datetime

    --SELECT @Datum = getdate()

    SELECT @Datum AS RealDate,

    CASE @Datum

    WHEN NULL THEN 'NULL'

    ELSE 'NOT NULL'

    END

    AS NULLed,

    CASE WHEN @Datum IS NULL THEN 'NULL'

    ELSE 'NOT NULL'

    END

    AS IsNulled

    SELECT @Datum AS RealDate,

    ISNULL(@Datum,NULL) AS NULLed

    --Ramesh


  • You can get around this by changing the way you test for NULL. By using "IS NULL" you'll get the desired results.

    I can't speak to the "WHY" the other didn't work, but perhaps someone else on here can.

    DECLARE @Datum datetime

    --SELECT @Datum = getdate()

    SELECT

    @Datum AS RealDate,

    CASE WHEN @Datum IS NULL

    THEN 'NULL'

    ELSE 'NOT NULL'

    END

    AS CASENULL

    SELECT

    @Datum AS RealDate,

    ISNULL(@Datum,NULL) AS [ISNULL]

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • *laughs* It seems Ramesh and I were working on the same solution at the same time. 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • May be not, its too late in the day:D

    --Ramesh


  • Tnx guys,

    I tried IS NULL but I guess I did something wrong there. This works & helps. Tnx again.

    Greetz,
    Hans Brouwer

  • Just in case anyone is interested, the reason that When Null did not work is because SQL tried to use an equality operator to compare the values. Null is the absence of a value, therefor it cannot be equal to anything.;)

  • DDarn! Rich beat me to the punch! :w00t:

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • David Paskiet (11/21/2007)


    DDarn! Rich beat me to the punch! :w00t:

    Close David, you were only 4 hours off :Whistling:

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply