locked
Arithmetic overflow RRS feed

  • Question

  • I am getting the following error running an sp.

    Msg 8115, Level 16, State 8, Procedure cc_updBillingBatchesTotal, Line 35

    Arithmetic overflow error converting int to data type numeric.

    Below is my sp.

    ALTER PROCEDURE [dbo].[cc_updBillingBatchesTotal]
    		@BillingBatchID		int
    
    AS 
    	SET NOCOUNT ON 
    	
    	BEGIN
    
    		DECLARE @ControlCount decimal(4,0);
    		DECLARE @ControlQty decimal(10,1);
    		DECLARE @ControlAmt decimal(16,2);
    		DECLARE @ErrorCount int;
    		DECLARE @RecCount int;
    
    		SELECT  @RecCount = COUNT(BillingDetailID), 
    				@ControlQty = SUM(qty), 
    				@ControlAmt = SUM(extprc),
    				@ErrorCount = SUM(CASE WHEN erritm = 1 OR errgrp = 1 OR errmbr = 1 OR errcu = 1 THEN 1
    									   ELSE 0
    									   END)
    		  FROM dbo.BillingDetail
    		GROUP BY BillingBatchID
    		HAVING (BillingBatchID = @BillingBatchID);
    
    		SET @ControlCount = CAST(@RecCount As decimal(4,0));
    
    		UPDATE dbo.BillingBatches
    		   SET ctrlcnt = ISNULL(@ControlCount,0.0), 
    			   ctrlqty = ISNULL(@ControlQty,0.0), 
    			   ctrlamt = ISNULL(@ControlAmt,0.0), 
    			   aclcnt = ISNULL(@ControlCount,0.0),
    			   aclqty = ISNULL(@ControlQty,0.0),
    			   aclamt = ISNULL(@ControlAmt,0.0),
    			   errors = CASE WHEN @ErrorCount > 0 THEN 1
    							 ELSE 0
    							 END,
    			   valcur = 1
    		 WHERE ([BillingBatchID] = @BillingBatchID);
    
    	END

    Wednesday, April 1, 2015 10:29 PM

Answers

  • The error message is clear. You want to put a value from integer data type into a column or variable with numeric data type. And the length of the integer number is more that the total number of decimal digits. 

    For example, a numeric(2,2) can accept integer values up to 9 and numeric values up to 9.9

    • Proposed as answer by Eric__Zhang Thursday, April 2, 2015 11:08 AM
    • Marked as answer by David Chase89 Thursday, April 2, 2015 11:33 AM
    Wednesday, April 1, 2015 10:43 PM

All replies

  • The error message is clear. You want to put a value from integer data type into a column or variable with numeric data type. And the length of the integer number is more that the total number of decimal digits. 

    For example, a numeric(2,2) can accept integer values up to 9 and numeric values up to 9.9

    • Proposed as answer by Eric__Zhang Thursday, April 2, 2015 11:08 AM
    • Marked as answer by David Chase89 Thursday, April 2, 2015 11:33 AM
    Wednesday, April 1, 2015 10:43 PM
  • If I am getting error on line 35 then which line is 35?  Where does line 1 start in error for sp?
    Wednesday, April 1, 2015 11:38 PM
  • To expand on Saeid Hasani comments, I'd say take a look at @ControlCount. A decimal(4,0) doesn't give you a lot of room to work.

    If for some reason you are limited to only 4 digits, I recommend adding "TOP 9999" to the select statement to keep @RecCount from overflowing @ControlCount.

    ... Not even going to ask why you're using @RecCount at all...


    Jason Long

    Wednesday, April 1, 2015 11:45 PM
  • The database was converted from old FoxPro app and that is what came across.  The largest decimal(4,0) on current database is under 1,000.  I put in @RecCount because I thought my error was due to setting @ControlCount = COUNT(*) of records affected.
    Wednesday, April 1, 2015 11:56 PM
  • Try executing the SELECT statement on it's own, without the complication of setting variables, and see what your data looks like and start debugging outwards from there.

    Sounds like you're getting an unexpected value somewhere. 


    Jason Long

    Thursday, April 2, 2015 12:08 AM
  • Hi,

    You right

    DECLARE @ControlCount decimal(4,0);

    This is not enough.

    SET decimal(10,0) at least or change type to int.

    and

    ALTER TABLE BillingBatches ALTER COLUMN ctrlcnt INT not null. WHAT type of column ctrlcnt in table definition ?


    Thursday, April 2, 2015 6:33 AM