Conversion failed when converting the varchar value int to data type int

Question

I want to multiple the value with 100 in my sql statement. But it showing error like Conversion failed when converting the varchar value 0.0035 to data type int.

And i know that the value is in varchar. But i want to multiple with 100

this is my original query code:

@WalCalc as VARCHAR(20) = NULL,
Set @SQLQuery = @SQLQuery + 'CAST(   (ISNULL(DI.Coupon,0)) AS varchar(50)) AS ''Coupon'',

and the converted query

Set @SQLQuery = @SQLQuery + 'CAST(   (ISNULL(DI.Coupon,0) * 100) AS Decimal(18,3)) AS ''Coupon'',

Please some one help me

Answer 1

I think you should be converting the Coupon column to a decimal first, and then multiplying by 100:

Set @SQLQuery = @SQLQuery + 'ISNULL(CAST(DI.Coupon AS Decimal(18, 3)), 0.0) * 100 AS ''Coupon'',