This is a very good question, thank you. Presence of nullif does not change anything though, so it is difficult to figure out why it is there. What I mean is that if the question is reduced to declaring and setting @c and @d only and the statements in question are then changed to
select coalesce(@c, @d);
select coalesce(@d, @c);
the result will still be the same, the first statement will fail and the second one will run just fine. I guess that this is because of the major difference between the behavior of coalesce with 2 parameters and isnull function which I believe would execute both statements just fine: coalesce always returns the expression of the highest precedence
data type regardless of the parameters' order while isnull returns the data type of the first parameter
. In other words, coalesce tries to cast first occurrence of the not null as datetime (datetime is of higher precedence). This is why it fails in the first statement, the first not null is @c which is equal to 'test', it tries to cast 'test' as datetime and fails. The second statement has the first not null is @d which is a datetime already, so the second statement executes without error.
Once again, thank you for an excellent question, I really enjoyed it.