Error "Conversion failed when converting the varchar value 'ABC' to data type int."

Question

Need some help with an error I am getting in SQL Server. I have the following lines in my query that is searching in column 'UserCode', and then sums column 'Amount' based on those codes. However, in the usercode column, there are some variables such as "ABC" instead of all integers. This causes an error "Conversion failed when converting the varchar value 'ABC' to data type int. I've tried searching but could not find a way around it. Could anyone please advise what would be the best way to overcome this error?

SUM((CASE WHEN (ec.UserCode >= 100 AND ec.UserCode <= 200) 
          THEN e.Amount 
          ELSE '0' 
     END)
   ) AS 'ColumnName'

Thanks!

Answer 1

The else is returning a string, so that is one problem. Another would be caused only if e.Amount were stored as a string -- horror of horrors, not storing a value with the correct type.

So, if that is the code that causing the problems, I would recommend:

SUM(CASE WHEN ec.UserCode >= 100 AND ec.UserCode <= 200
         THEN TRY_CONVERT(?, e.Amount)
         ELSE 0
    END) AS ColumnName

? is a place holder for the appropriate type, which might be int, bigint, decimal(20, 4) or something similar.

I should note: The error could also be caused if UserCode is a string. For that, use string comparisons:

SUM(CASE WHEN ec.UserCode >= '100' AND ec.UserCode <= '200'
         THEN TRY_CONVERT(?, e.Amount)
         ELSE 0
    END) AS ColumnName

Or, if you prefer, try_convert() there:

SUM(CASE WHEN TRY_CONVERT(int, ec.UserCode) >= 100 AND
              TRY_CONVERT(int, ec.UserCode <= 200
         THEN TRY_CONVERT(?, e.Amount)
         ELSE 0
    END) AS ColumnName

Answer 2

You could use a TRY_CONVERT which will return null if the conversion fails Then you can test for null or replace those with a 0

CASE WHEN TRY_CONVERT(float, ec.UserCode) IS NULL   
    THEN 0  
    ELSE CONVERT(float, ec.UserCode)

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15