Error converting data type varchar to numeric when casting varchar(max) to decimal

Question

I'm trying to sum values from two strings. In order to do that I'm casting the strings in to decimals, add them up and then cast it back to string for the output. Also using max to avoid duplication entries. I have the following line in my SP:

Cast( Max(CASE WHEN QuestionID = 261 THEN CAST(Answer AS decimal) ELSE '' END) + Max(CASE WHEN QuestionID = 260 THEN CAST(Answer AS decimal) ELSE '' END) AS varchar) as Total

The Answer field is varchar(max) and default value is ''.
The problem is that I'm getting the error message: Error converting data type varchar to numeric

Answer 1

The part that is failing is the inner case:

CASE WHEN QuestionID = 261
     THEN CAST(Answer AS decimal)
     ELSE '' END

A CASE expression (e.g.above) must return one datatype only. In this case, it cannot both return a decimal and a varchar (empty string ''). Due to type precedence the result from either branch is cast to a decimal. If ANY row has QuestionID != 261, it will try to cast that '' into decimal, and bam - your error.

You need this:

Cast( Max(CASE WHEN QuestionID IN (261,260) AND ISNUMERIC(ANSWER) = 1
               THEN CAST(Answer AS decimal)
               ELSE 0 END)
   AS varchar) as Total