The Excluded Possibility

Consider this code:

SELECT
   Value =
      CASE      
         WHEN Value IS NULL AND Text IS NOT NULL THEN Text
         WHEN Text IS NULL AND Value IS NOT NULL THEN Value
         ELSE NULL
      END

Does a problem leap out at you? It should. What if Value and Text are both not NULL? Then the whole expression is NULL. There's no WHEN Text IS NOT NULL AND Value IS NOT NULL THEN ... case.

Maybe the query can never return a non-NULL value for both of the columns at the same time. But in that case, why bother checking for the NULLity of the other column in each condition? It doesn't make sense.

This can be rewritten much more sensibly:

SELECT
   Value = IsNull(Value, Text)

It's that simple. If the two both can't be non-NULL at the same time, it will return the same value as the whole prior expression. If they can be both non-NULL at the same time, well, we have a problem... but no bigger a problem than the previous one. We would simply show one of the columns instead of a NULL. Perhaps here is the real solution:

SELECT
   Value = IsNull(Value, '') + IsNull(Text, '')

Something along these lines (perhaps with a separator in the case they both are non-NULL) is the only thing that makes sense in terms of explicitly checking the NULLity of both columns.