SQL Case - Syntax error converting the varchar value

variable order by when mixing datatypes? [Archive] - dBforum...

An excerpt from the above link that helped me figure out a problem I was having when using a case statement to compare strings and ints and was getting unexpected results:

" In T-SQL, a CASE expression has its data type determined before the query is processed, and the type of the CASE expression is the lowest precedence type that is at least as high a precedence of each of the CASE alternatives. This means that a CASE expression with both varchar and int alternatives will be typed as int. The result of this is that whenever any of the alternatives is evaluated, it will be interpreted as an int, which in the case of a non-numeric varchar string, can cause a run-time error. If the value of a parameter is such that the non-numeric varchar is never accessed, this won't cause an error. You might think it would be better if the CASE expression weren't typed, but that would leave undetermined the question of how something like MAX(CASE when ColumnA = 0 then ColumnB else ColumnC end) should be evaluated. There is another alternative to handle this, and that is to cause the CASE expression to be of type sql_variant (needs SQL Server 2000), to which any numeric or varchar value can be cast, and which will preserve the correct ordering of each column according to its base type: "