Arithmetic overflow error converting expression to data type smallmoney

Posted on 2009-02-12
Last Modified: 2012-05-06
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!!!!
Question by:jmarkfoley
    LVL 22

    Accepted Solution


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


    LVL 22

    Expert Comment

    And the total that you're trying to assign to @balance is 219,920.9.
    LVL 11

    Expert Comment

    Pivar is correct. Can you assign @balance as money instead of smallmoney?
    LVL 1

    Author Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now