I received an error message when converting data type varchar to numeric
To solve the conversion error, you need to remove the comma (,) from the varchar value you want to convert to numeric.
It would help if you also ensured the value varchar to be converted is the number you need to convert to the numeric data type. You need to check that you use only the decimal symbol, the dot, and not any digit grouping symbols.
When you need to convert varchar values to numbers (i.e., decimal, numeric, etc.), you need to be careful with your varchar value. It does not need to include any digit grouping symbols (i.e., a comma) or other characters that do not have a meaning as a number.
The error happens when adding a string and a numeric value. More likely, you must use:
insert into #Tmp
SELECT 1,'TDS @'+CAST(T1.rate AS VARCHAR(30)) + ' %'
FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.AbsEntry
It would help if you converted the rate to a string before adding it to another string. Though you also have the FORMAT function depending on your SQL Server, you need to avoid doing that on the SQL Server and instead serve "raw" data that are formatted on the client-side as you need:
- it is easier to support multiple languages and the appropriate separator- you can apply new rules any time, such as showing the rate using a red color if too low or too high.
I hope it helps!