• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 817
  • Last Modified:

Arithmetic overflow error converting expression to data type smallmoney

I have a stored procedure that has been running fine for serveral month, and appears to run fine for most cases for rows processed, but is blowing up with the arithmentic overflow consistently at the same point. The function calculated interest and adds interest and contributions to a balance for some number of contributions (payments). Here is the code:

declare  @amount smallmoney,
  @balance smallmoney,
  @interest smallmoney

  set @balance = @balance + @interest + @amount

To debug, I print balance, interest and amount for each iteration. When they have the following values:

balance: 213882.22
interest: 35.56
amount: 6003.12

I get the error:
 
Msg 8115, Level 16, State 2, Line 160
Arithmetic overflow error converting expression to data type smallmoney.

Note, the "@balance = @balance + @interest + @amount" is line 160.

None of these values seem to exceed the smallmoney range. What's up? This is preventing us from printing payroll checks!!!!
0
jmarkfoley
Asked:
jmarkfoley
  • 2
1 Solution
 
pivarCommented:
Hi,

Min and max for smallmoney is - 214,748.3648 to 214,748.3647 so you have to change @balance to another type.

/peter

0
 
pivarCommented:
And the total that you're trying to assign to @balance is 219,920.9.
0
 
dodge20Commented:
Pivar is correct. Can you assign @balance as money instead of smallmoney?
0
 
jmarkfoleyAuthor Commented:
I changed it to decimal(14,5), but money would have probably worked too. I don't know why, but I was reading the smallmoney limit as 214 million, not 214 thousand. My bad. Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now