Tuesday, July 30, 2013

Arithmetic overflow error converting numeric to data type numeric - SQL 2008

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


No comments:

Post a Comment