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

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

Answers


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

Need Your Help

Django “required” attribute/class not refreshing

html django request

I have a form for which I would like to toggle a field's required attribute on and off. I can successfully use the required attribute of the Form's CharField but only on a page refresh or when I P...

Targets for conditional comments in html?

html conditional-comments

I know you can target browsers with conditional comments, as explained here in MSDN.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.