The rules for determining the datatype of a case expression are based on the same rules that determine the datatype of a column in a union operation. A case expression has a series of alternative result expressions (R1, R2, ..., Rn in the example below) which are specified by the then and else clauses. For example:
case when search_condition1 then R1 when search_condition2 then R2 ... else Rn end
The datatypes of the result expressions R1, R2, ..., Rn are used to determine the overall datatype of case. The same rules that determine the datatype of a column of a union that specifies n tables, and has the expressions R1, R2, …, Rn as the ith column, also determine the datatype of a case expression. The datatype of case is determined in the same manner as by the following query:
select...R1...from ... union select...R2...from... union... ... select...Rn...from...
Not all datatypes are compatible, and if you specify two datatypes that are incompatible (for example, char and int), your Transact-SQL query fails. See the Reference Manual: Building Blocks.
