none
Arithmetic overflow error converting numeric to data type numeric - SQL 2008 RRS feed

  • Question

  • Msg 8115, Level 16, State 8, Line 178
    Arithmetic overflow error converting numeric to data type numeric.
    The statement has been terminated.

    I have a report that calls the below mentioned SQL Function cusPovLevel_Revision. Above is the error I got back when running a set date range. After some investigating, I found the client to have a few patients with an annual income of 999999996.00. Obviously a data entry error. I have tried mofifying the function to account for this large number and cant seem to make it work right. Any help is appreciated.

    USE [demo];
    GO
    /********************************************************************************************************
    ***** Object:  UserDefinedFunction [dbo].[cusPovLevel_Revision]    Script Date: 07/30/2013 11:05:18 *****
    ********************************************************************************************************/
    SET ANSI_NULLS ON;
    GO
    SET QUOTED_IDENTIFIER ON;
    GO
    ALTER FUNCTION dbo.cusPovLevel_Revision
        (
          @liIncome INT ,
          @liFamilySize TINYINT ,
          @lcSFEffectiveDte CHAR(8)
        )
    RETURNS INT
    AS 
        BEGIN
            DECLARE
                @liBaseIncome NUMERIC(12 , 2) ,
                @lcPayPovCode CHAR(3) ,
                @liPayPovID INT ,
                @lcFamilySize CHAR(3) ,
                @liPctPov DECIMAL(8 , 3) ,
                @lcCode VARCHAR(200) ,
                @liMedListsId INT ,
                @liRetVal INT ,
                @lnCsrBaseIncome NUMERIC(12 , 2) ,
                @lcCsrEffDte CHAR(8) ,
                @lnRetValBaseIncome NUMERIC(12 , 2) ,
                @lnIncome NUMERIC(12 , 2) ,
                @lnCode NUMERIC(12 , 2) ,
                @liSFScheduleID INT; 
            IF @liFamilySize > 15 
                BEGIN
                    SET @lcFamilySize = '15';
                END
            ELSE 
                BEGIN
                    SET @lcFamilySize = CAST(@liFamilySize AS VARCHAR);
                END;
            DECLARE @csrBaseIncome CURSOR;
            SET @csrBaseIncome = CURSOR
            FOR SELECT BaseIncome = CAST( description AS numeric( 12 , 2 )) , 
                       EffectiveDteString = LEFT( LTRIM( FunctionName ) , 8 )
                  FROM cusCRIMedLists
                  WHERE code = @lcFamilySize
                    AND tablename =  'baseincome'
                    AND FunctionName IS NOT NULL
                  ORDER BY EffectiveDteString DESC;
            SET @lnRetValBaseIncome = -1.00;
            OPEN @csrBaseIncome;
            FETCH NEXT FROM @csrBaseIncome INTO @lnCsrBaseIncome , @lcCsrEffDte;
            WHILE @@FETCH_STATUS = 0 
                BEGIN
                    IF @lcSFEffectiveDte >= @lcCsrEffDte 
                        BEGIN
                            SET @lnRetValBaseIncome = @lnCsrBaseIncome;
                            BREAK;
                        END;
                    FETCH NEXT FROM @csrBaseIncome INTO @lnCsrBaseIncome , @lcCsrEffDte;
                END;
            CLOSE @csrBaseIncome;
            DEALLOCATE @csrBaseIncome;
            IF @lnRetValBaseIncome = -1.00 
                BEGIN
                    RETURN 0;
                END;
            ELSE 
                BEGIN
                    SET @liBaseIncome = @lnRetValBaseIncome;
                    SET @lnIncome = CAST(@liIncome AS NUMERIC(12 , 2));
                    SET @liPctPov = @liIncome / @liBaseIncome * 100;
                    DECLARE @CrsrVar CURSOR; 
                    SET @CrsrVar = CURSOR
                    FOR SELECT Code , 
                               MedListsId
                          FROM cusCRIMedLists
                          WHERE TableName = 'SlidingFeeScheduleDtl'
                            AND JoinId IN( 
                                           SELECT MedListsId
                                             FROM MedLists
                                             WHERE tablename = 'SlidingFeeSchedule' AND Code = 'S'
                                         )
                          ORDER BY CAST( Code AS INT );
                    OPEN @CrsrVar;
                    FETCH NEXT FROM @CrsrVar INTO @lcCode , @liMedListsId;
                    WHILE @@FETCH_STATUS = 0 
                        BEGIN
                            SET @liRetVal = @liMedListsId;
                            SET @lnCode = CAST(@lcCode AS NUMERIC(12 , 2));
                            IF @liPctPov <= @lnCode 
                                BEGIN
                                    BREAK
                                END;
                            FETCH NEXT FROM @CrsrVar INTO @lcCode , @liMedListsId;
                        END;
                    CLOSE @CrsrVar;
                    DEALLOCATE @CrsrVar;
                    DECLARE @liPctPovInt INT;
                    SET @liPctPovInt = ROUND(@liPctPov , 0);
                    RETURN ROUND( @liPctPovInt , 0);
                END;
            RETURN 0;
        END;
    GO

    Tuesday, July 30, 2013 4:40 PM

Answers

  • I'm guessing NUMERIC(12, 2) is the datatype used in the underlying table? I would focus on @liPctPov, which is declared DECIMAL(8 , 3).  This could exceed its range, depending on @liBaseIncome:

    SET @liPctPov = @liIncome / @liBaseIncome * 100;
    I would give @liPctPov more space (more detail here.)
    • Marked as answer by Jeffs1977 Tuesday, July 30, 2013 7:43 PM
    Tuesday, July 30, 2013 5:22 PM
  • I see 2 errors:

    the line 

    SET @liPctPov = @liIncome / @liBaseIncome * 100;

    should be 

    SET @liPctPov = @lnIncome / @liBaseIncome * 100;

    and @liPctPov should be declared with at least a +2 precision more than @lnIncome:

    DECLARE @liPctPov NUMERIC(14 , 2);

    (in the worst case @liPctPov=(+|-)@lnIncome*100)





    • Edited by dsmwb Tuesday, July 30, 2013 5:45 PM typo
    • Proposed as answer by dsmwb Tuesday, July 30, 2013 5:51 PM
    • Marked as answer by Jeffs1977 Tuesday, July 30, 2013 7:42 PM
    Tuesday, July 30, 2013 5:30 PM

All replies

  • I'm guessing NUMERIC(12, 2) is the datatype used in the underlying table? I would focus on @liPctPov, which is declared DECIMAL(8 , 3).  This could exceed its range, depending on @liBaseIncome:

    SET @liPctPov = @liIncome / @liBaseIncome * 100;
    I would give @liPctPov more space (more detail here.)
    • Marked as answer by Jeffs1977 Tuesday, July 30, 2013 7:43 PM
    Tuesday, July 30, 2013 5:22 PM
  • Dont find an issue with this value, any other value could be an issue.

    declare @test numeric(12,2)
    set @test=999999996.00
    select @test
    set @test=cast(999999996.00 as numeric(12,2))
    select @test


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 5:22 PM
  • I see 2 errors:

    the line 

    SET @liPctPov = @liIncome / @liBaseIncome * 100;

    should be 

    SET @liPctPov = @lnIncome / @liBaseIncome * 100;

    and @liPctPov should be declared with at least a +2 precision more than @lnIncome:

    DECLARE @liPctPov NUMERIC(14 , 2);

    (in the worst case @liPctPov=(+|-)@lnIncome*100)





    • Edited by dsmwb Tuesday, July 30, 2013 5:45 PM typo
    • Proposed as answer by dsmwb Tuesday, July 30, 2013 5:51 PM
    • Marked as answer by Jeffs1977 Tuesday, July 30, 2013 7:42 PM
    Tuesday, July 30, 2013 5:30 PM