Multiplying 2 column values in Dynamic SQL

DECLARE updatesql nvarchar(MAX)

SET @updatesql = N'update '+ @TableName +'  SET      
 Total_Revenue=CAST(COALESCE(Total_Hours,0) AS FLOAT) * CAST(COALESCE(Rate,0) AS FLOAT)
 where associate_ID='+ @associateId 

 EXECUTE  (@updatesql)

This is giving the error message: Conversion failed when converting the varchar value ‘27.72081’ to data type int.

Answer

You’re casting numeric values to VARCHAR inside your dynamic statement. Remove the CASTs and it should be good.

Your resulting statement should read something like this when printed:

update table1 SET      
 Total_Revenue=COALESCE(Total_Hours,0) * COALESCE(Rate,0)
 where associate_ID=1;

You should also use 0.0 in your COALESCE so that your formula multiplies a decimal type with another decimal type. As is you are multiplying a decimail and an INT. (I assume that Total_Hours and/or Rate is a decimal type based on the value in the error message you provided.)

Update after question edit:

Your CAST is in the wrong place. You can’t COALESCE a VARCHAR and an INT. Your statement should now look something like this:

update table1 SET      
 Total_Revenue=COALESCE(CAST(Total_Hours AS FLOAT),0.0) * COALESCE(CAST(Rate AS FLOAT),0.0)
 where associate_ID=1;