I am assigned with a task to debug an existing ASP.NET C# system. I found out that this is caused by a stored procedure, and the error message is
Msg 8114, Level 16, State 5
Error converting data type varchar to real.
This is too long for a comment.
The problem is obvious -- you have a string and you are treating it as a number. There are three possible reasons.
The first is an explicit conversion where the string is converted to a number. This should be easy to find. You can replace the explicit conversion with
try_convert() in SQL Server 2012+.
The second is not much harder. The
+ operator is used both for addition and string concatenation. It often causes this type of problem. All numeric arguments need to be converted to strings. Or, change all code that does concatenation to use the
CONCAT() function -- the arguments are automatically converted to strings.
The third is implicit conversion for numeric operations. If you have any other operations (or function calls) that require numeric values, then SQL Server will implicitly convert the values to numbers. This can be very hard to find. Try to write code that does not rely on implicit conversions.
Note: Trying to filter out bad values using a
WHERE clause (even in a subquery or CTE) does not work. If you hae