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.