Chris Johnson troubleshoots an issue in code:
The poster had a
CASEstatement and was wondering why it didn’t work as expected. Essentially they were doing something like:
CASE WHEN @a = @b OR (@a IS NULL AND @b IS NULL) THEN 1 ELSE 0
CASE WHEN NOT(@a = @b OR (@a IS NULL AND @b IS NULL)) THEN 1 ELSE 0
And they wanted to know why both were returning 0 when
@bwere set to
NULL. The issue here is that any normal predicate involving
NULLreturns an unknown. They had tried to compensate with the
OR, which got them the result they wanted in the first statement, but didn’t understand why it did that.
Click through for the explanation.