User1126057398 posted
I had created a dynamic query in which I have to multiply the table column with variable, but it's giving error 'Arithmetic overflow error converting varchar to data type numeric.':. How can this be achieved?
SET @Sql = ';WITH x AS
(
SELECT MET_ACTIVITY_DESCRIPTION, num, sn
FROM (
SELECT MET_ACTIVITY_DESCRIPTION,
[Exercise Category] = CONVERT(varchar(32), Category),
[MET VALUE] = CONVERT(varchar(32), MET_VALUE),
[Calories Burn/Hour] = CONVERT(varchar(32), (MET_VALUE*' + @weight*1.0 + '))
FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
) AS d
UNPIVOT (num FOR sn IN
([Exercise Category], [MET VALUE], [Calories Burn/Hour])
) AS unp
)
SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToCompare + ' FROM x
PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;'
