Case expression with NULL

  • Comments posted to this topic are about the item Case expression with NULL

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Now THERE'S and SQL "Oolie" if I ever saw one!  Nice job, Mr. Collins.  And, yep, it IS clearly documented.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • But what about this? It returns null in both cases....

    declare @t int

    select case when @t is null then @t else null end

  • The reason for the restriction is (I assume) that it needs some way of determining datatype. So this will also work:

    select case when 1=0 then cast(null as int) end;
  • Peter.Frissen wrote:

    But what about this? It returns null in both cases....

    declare @t int

    select case when @t is null then @t else null end

    The error specifically states it can't be a NULL constant; a variable with the value NULL isn't a NULL constant. This is also why CAST(NULL as int) works, as it's not just the NULL constant, it's a CAST function which contain s the NULL constant.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Also, for those interested, this error occurs at compile/parsing rather than at execution. Take the following:

    SELECT 1/0;

    SELECT CASE WHEN 1 = 0 THEN NULL END;

    You might expect to get the error "Divide by zero error encountered", however, the error you get is "At least one of the result expressions in a CASE specification must be an expression other than the NULL constant." This is because none of the statements are actually run, the compiler sees that the CASE expression only has constant NULL values for its expressions and so it generates an  error then.

    This is also a further reason why using a variable doesn't cause the error. When being compiled/parsed the data engine doesn't know what the variable's values will be (even if they are assigned a constant earlier in the batch). So it doesn't matter that the constant NULL was assigned earlier to the variable, as that isn't used to validate the batch during the pre-execution processes (against the CASE).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • These are questions that reminds one to read all the documentation for a statement. Which is why I am one of the many that selected NULL as my answer. Great question.

  • This was removed by the editor as SPAM

  • The question came up based on this forum post.  Not sure I'd know the answer otherwise

    https://www.sqlservercentral.com/forums/topic/nullif-in-ssis#post-4122037

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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