Case expression with NULL

Question

Post reply

Case expression with NULL

Steve Collins

SSCrazy

Points: 2075
More actions
January 23, 2023 at 12:00 am

#4138243

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
January 24, 2023 at 7:09 am

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

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)

Peter.Frissen SSCrazy Points: 2101 More actions · Answer 2

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

declare @t int

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

Toreador SSChampion Points: 11360 More actions · Answer 3

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;

Thom A SSC Guru Points: 99126 More actions · Answer 4

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

Thom A SSC Guru Points: 99126 More actions · Answer 5

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

Bob Razumich SSCrazy Points: 2137 More actions · Answer 6

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.

Steve Collins SSCrazy Points: 2075 More actions · Answer 7

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