More Fun With NULL

Question

Post reply

More Fun With NULL

Viewing 4 posts - 16 through 18 (of 18 total)

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

KevinC. SSCommitted Points: 1570 More actions · Answer 1

Nice question. It really got me thinking (and on a Monday, too!):w00t:

christopher.gray Old Hand Points: 372 More actions · Answer 2

About the author's discussion of Option 5...

"Option 5 is am especially delicious failure in that it looks just like the sucessful option 3, only using ISNULL instead of COALESCE. It fails because the value that ISNULL will substitute for a found NULL must be the same (or capable of being implicitly converted to the same) datatype as the tested value. The COALESCE function does not enforce datatyping, so Option 3 works as is, but in a broader scope, one should beware of how the output of the COALESCE is used -- it may still subsequently encounter a datatype mis-match "

To me, it seems like this is implying that COALESCE doesn't type-check, but the examples don't compare apples to apples.... "The successful Option 3" looks like this:

coalesce(convert(char(10),@MyDate,101),'Date is Null')

... the use of CONVERT means both parameters to COALESCE are varchars. Option 3 doesn't skip type-checking - the (base) types just happen to be the same. Try it like this:

coalesce(@MyDate,'Date is Null')

... and you get the same error the IsNull in Option 5 gives.

john.arnott SSChampion Points: 11882 More actions · Answer 3

Christopher,

You're absolutely right. I feel a bit embarrassed, but have to agree. Your counter example is actually in the list as option 1, so I have no excuse.

So, it looks as though the one difference is how ISNULL will use a stricter version of the first element's data description, truncating the contents of the second element to that length. Compare successful option 3 with truncated option 6:

Select coalesce(convert(char(10),@MyDate,101),'Date is Null') --returns 'Date is Null'

Select isnull(convert(char(10),@MyDate,101),'Date is Null') --returns 'Date is Nu'

Even though the CONVERT specifies 10 characters in both, only the ISNULL truncates the literal message.

Thank you again for pointing out my confusion with option 5.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

:-)Excellent Question........

Thanks a lot

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/