Also, for those interested, this error occurs at compile/parsing rather than at execution. Take the following:
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
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.