Arithmetic overflow error message in SQL Server 2008

Question

In my stored procedure I'm inserting some data into a temporary table and then selecting them. But for several hours I can't resolve the following problem. I tried many solutions, but I keep getting the following error:

Arithmetic overflow error converting numeric to data type numeric.

Here is my SQL Server 2008 stored procedure:

ALTER procedure [dbo].[spr_Remuneration_Report2]
   @Action int = 1,
   @AgentID int = null,
   @ProviderID int = null,
   @date1 datetime = null,
   @date2 datetime = null
as
   if @Action = 1
   begin
      declare @tblAgents as table(AgentID int, ProviderID int, 
                                  TurnOver decimal(8, 2) null, 
                                  ProviderSum decimal(8, 2) null)

      declare @tmp as table(AgentID int)

      insert into @tmp
         select AgentID 
         from Agents 
         where parentID = @AgentID and agentType = 0

      while exists(select top(1) * from @tmp)
      begin
         declare @tmpAgentID as int

         select top(1) @tmpAgentID = agentid  
         from @tmp

         insert into @tblAgents
            select @tmpAgentID, @ProviderID, SUM(pr.PaySum), SUM(pr.ProviderSum)
            from Payment_Report pr
            inner join Agents a on pr.AgentID = a.AgentID
            where a.hrccrt like '%.' + CAST(@tmpAgentID as nvarchar(10)) + '.%' 
              and pr.ProviderID = @ProviderID
              and pr.Status = 2 
              and (pr.RegDateTime between @date1 and DATEADD(s, -1, @date2))
                --
         delete from @tmp where AgentID = @tmpAgentID
      end

      select 
          tb.AgentID, (a.name + ' ' + a.surname) as AgentName, tb.ProviderID,
          p.Name as Provider, tb.TurnOver, tb.ProviderSum,
          SUM(cast(tb.ProviderSum * ap.Remuneration / 100 as decimal(5, 2))) as Remuneration
      from 
          @tblAgents tb
      inner join 
          Agents a on tb.AgentID = a.AgentID
      inner join 
          Agent_Provider ap on tb.ProviderID = ap.ProviderID and tb.AgentID = ap.AgentID
      inner join 
          Provider p on tb.ProviderID = p.ID
      where 
          ap.Enabled = 'True' and tb.TurnOver is not null
      group by 
          tb.AgentID, a.name+ ' ' + a.surname, tb.ProviderID, p.Name, 
          tb.TurnOver, tb.ProviderSum
    end

You are trying to insert a value that is too big to fit in one of the numeric columns. Be aware that decimal(8, 2) can hold a total of 8 digits, not 8 digits before the decimal and 2 after. — D Stanley
just try to work with decimal(5,2) decimal(8, 2) decimal(11, 2) or decimal(10, 2) and Replace INT to BIGINT — mohan111
If you were able to get an answer, please post it as an answer and accept it. This will help anyone else with the same issue. — Ryan Gates

firefalcon firefalcon · Accepted Answer · 2014-05-30T15:32:37

The issue is solved by replacing decimal(5, 2) to decimal(11, 2) in select statement. The rest of the code seems correct in my case.

Arithmetic overflow error message in SQL Server 2008

1 Answers