Arithmetic overflow error converting nvarchar to data type numeric when creating string

Question

Trying to figure out why I am getting this error that happens when creating a string to exec

SET @SQLINSERTSTRINGFINAL = @SQLINSERTSTRING + @Suggested_Qty+''','''+
    @Required_Qty+''','''+
    @System_Cost+''','''+
    @Revised_Cost

These 4 variables are populated from

convert(numeric(7,2),ltrim(rtrim(floor((case when r.calc_qty > 99999 then 99999 else r.calc_qty end)/(case when s.PLBCF_1 is null or s.PLBCF_1 =0 then 1 else s.PLBCF_1 end))))),
    convert(numeric(7,2),ltrim(rtrim(floor((case when r.po_number <> 'NONE' then r.calc_qty else case when r.adj_qty > 99999 then 99999 else r.adj_qty end end)/(case when s.PLBCF_1 is null or s.PLBCF_1 = 0 then 1 else s.PLBCF_1 end))))), 
    convert(numeric(9,4),ltrim(rtrim(case when s.std_cost is null then r.std_cost else s.std_cost end))),
    convert(numeric(9,4),ltrim(rtrim(r.std_cost)))

I've check every table used and they are consistent in the column type. Only non numeric in there is r.po_number which can be of value 'NONE'

Did you verify all your numbers are within the right number of digits for the field. Example if you have a Numeric 7,2 are their more digits than that. Usually "Arithmetic Overflow" would be related to that in my experiences. — logixologist
– logixologist, Commented May 13, 2013 at 17:22

Derek · Accepted Answer · 2013-05-13 17:22:50Z

1

You need to convert each numeric to a string when you're setting the final string value like this:

SET @SQLINSERTSTRINGFINAL = @SQLINSERTSTRING + convert(nvarchar(max), @Suggested_Qty)+''','''+
    convert(nvarchar(max), @Required_Qty)+''','''+
    convert(nvarchar(max), @System_Cost)+''','''+
    convert(nvarchar(max), @Revised_Cost)

It won't auto-cast for you

answered May 13, 2013 at 17:22

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joshua Lockhart Over a year ago

Thanks, I don't know why I didn't see that at all as I try to research as much as I can before I ask questions. Once it allows me to I am going to accept this answer.

Collectives™ on Stack Overflow

Arithmetic overflow error converting nvarchar to data type numeric when creating string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related