SQL Conversion failed when converting the varchar value to data type numeric

Question

I'm having some difficulty with the following SQL query:

SELECT
    SUM(ArTrnDetail.NetSalesValue) AS [Price],
    'ExecuteSubReport(813, 0, Job)' AS [LaborCost],
    'ExecuteSubReport(815, 0, Job)' AS [MaterialCost],
    'ExecuteSymbolicMath(LaborCost + MaterialCost)' AS [TotalCost],
    'ExecuteSymbolicMath(Price - (LaborCost + MaterialCost))' AS [Margin],
    CASE 
        WHEN SUM(ArTrnDetail.NetSalesValue) = 0 
            THEN 0
            ELSE 'ExecuteSymbolicMath(1 - (TotalCost / Price))'  <-- Where it's failing
    END AS [MarginPct]

The ExecuteSubReport and ExecuteSymbolicMath are company functions. The ExecuteSymbolicMath basically strips unwanted chars like $ and commas, does the math and returns the result as a string. The end result for the column being a decimal(2 places) between 0-1. When I try to run the query I get an error saying that it can't convert varchar to numeric. I've tried just about everything I can think of. I've tried replace, convert, cast, str.

Thank you very much for your help!

Answer 1

A SQL expression has exactly one type. A case expression is no different.

According to the rules of SQL, numbers trump strings, so SQL tries to convert a string to a number -- and that is what is happening.

Just use '0' instead of 0 in the case expression:

(CASE WHEN SUM(ArTrnDetail.NetSalesValue) = 0 
      THEN '0'
      ELSE 'ExecuteSymbolicMath(1-(TotalCost/Price))'  <-- Where it's failing
  END) AS [MarginPct]

Or, if you actually want a calculation, get rid of all the single quotes:

(CASE WHEN SUM(ArTrnDetail.NetSalesValue) = 0 
      THEN 0
      ELSE ExecuteSymbolicMath(1-(TotalCost/Price)) 
 END) AS [MarginPct]