Cause of arithmetic overflow error?

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 30, 2019 at 3:45 pm

  #370386

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 30, 2019 at 4:10 pm

  #2020646

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 30, 2019 at 4:19 pm

  #2020647

  Precision, scale, and Length

  Because NUMERIC(4,2) sets two spaces for decimals, which only leaves two spaces for the integer portion.  Since 50*10=500 and 500 requires three integer spaces, it overflows.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 30, 2019 at 4:26 pm

  #2020648

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 30, 2019 at 5:17 pm

  #2020649

  drew.allen - Wednesday, January 30, 2019 4:19 PM

  Precision, scale, and Length

  Because NUMERIC(4,2) sets two spaces for decimals, which only leaves two spaces for the integer portion.  Since 50*10=500 and 500 requires three integer spaces, it overflows.

  Drew

  This is a very helpful explanation.  Thank you.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 30, 2019 at 6:38 pm

  #2020650

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 30, 2019 at 10:54 pm

  #2020657

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 31, 2019 at 7:45 am

  #2020700

  michael.leach2015 - Wednesday, January 30, 2019 10:54 PM

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

  Sorry I meant precision on the last sentence.

  Sue

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 31, 2019 at 8:44 am

  #2020715

  Sue_H - Thursday, January 31, 2019 7:45 AM

  michael.leach2015 - Wednesday, January 30, 2019 10:54 PM

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

  Sorry I meant precision on the last sentence.

  Sue

  So you mean numeric (6,8)?  Doesn't the precision always have to be greater than the scale?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 31, 2019 at 9:16 am

  #2020721

  michael.leach2015 - Thursday, January 31, 2019 8:44 AM

  Sue_H - Thursday, January 31, 2019 7:45 AM

  michael.leach2015 - Wednesday, January 30, 2019 10:54 PM

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

  Sorry I meant precision on the last sentence.

  Sue

  So you mean numeric (6,8)?  Doesn't the precision always have to be greater than the scale?

  No, the scale can never be larger than the precision. He means numeric(6,2) instead of numeric(8,4)
  EDIT: No to the first question, Yes to the second one.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 31, 2019 at 9:20 am

  #2020723

  michael.leach2015 - Thursday, January 31, 2019 8:44 AM

  Sue_H - Thursday, January 31, 2019 7:45 AM

  michael.leach2015 - Wednesday, January 30, 2019 10:54 PM

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

  Sorry I meant precision on the last sentence.

  Sue

  So you mean numeric (6,8)?  Doesn't the precision always have to be greater than the scale?

  Yes, precision has to be greater than the scale, but precision is the first number.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 31, 2019 at 9:45 am

  #2020728

  I mixed them up when I posted and didn't make it clear enough on the truncation.
  I'm not sure it really matters if trying for the minimum - the storage is the same with precision 1-9. Which is why I would have just left it.

  Sue

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 31, 2019 at 10:30 am

  #2020739

  Luis Cazares - Thursday, January 31, 2019 9:16 AM

  michael.leach2015 - Thursday, January 31, 2019 8:44 AM

  Sue_H - Thursday, January 31, 2019 7:45 AM

  michael.leach2015 - Wednesday, January 30, 2019 10:54 PM

  Sue_H - Wednesday, January 30, 2019 6:38 PM

  michael.leach2015 - Wednesday, January 30, 2019 4:26 PM

  Sue_H - Wednesday, January 30, 2019 4:10 PM

  michael.leach2015 - Wednesday, January 30, 2019 3:45 PM

  What is causing this error in the following code:

  Msg 8115, Level 16, State 8, Procedure sp_calcArea, Line 10 [Batch Start Line 18]
  Arithmetic overflow error converting numeric to data type numeric.

  I suspect the error has something to do with the PRINT statement.  I ran the same code yesterday with INT as the data types and I didn't get this error.  What is causing this error?
  
CREATE PROCEDURE sp_calcArea

  @length numeric (4,2),
  @width numeric (4,2),
  @area numeric (4,2) OUTPUT

  AS

  BEGIN
   SELECT @area = @length * @width
  END

  GO

  
  DECLARE @showArea numeric (4,2)

  EXECUTE sp_calcArea
   50, 10, @area = @showArea OUTPUT

  PRINT @showArea

  It's not the print statement. It's the precison and scale for area or whatever you use for the results. The precision and scale change for the results when performing different operations. Refer to the table in this documentation:
  Precision, scale, and Length (Transact-SQL)

  Sue

  Thank you for the reference.  I changed my stored procedure so that the @area variable has a data type of numeric (9,4) instead of numeric (4,2).  In the declaration of @showArea before the execute statement, I changed the data type to @showArea numeric (9,4) from @showArea numeric (4,2).  This worked.

  But just to be sure, did I changed the precision and scale to the correct minimum amounts or did I use more than I needed?

  
  

  According to the documentation that would be correct.
  You can truncate after the decimal but it's not the same result if needed. I'm not sure that it would need more than 8 for the scale but there is likely some nuance thing I'm not thinking of. Otherwise, it seems that 8 would be the minimum 99.99 * 99.99. If you truncated the results to 2, you would need a scale of 6.

  Sue

  When I do 99.99 x 99.99 I get 9998.0001

  Instead of 6, wouldn't the scale be 4 since there are 4 digits to the right of the decimal?

  Sorry I meant precision on the last sentence.

  Sue

  So you mean numeric (6,8)?  Doesn't the precision always have to be greater than the scale?

  No, the scale can never be larger than the precision. He means numeric(6,2) instead of numeric(8,4)
  EDIT: No to the first question, Yes to the second one.

  Thank you.  So numeric (6,2) if I want to have 2 digits after the decimal point.  Thank you for clarifying.

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 31, 2019 at 10:31 am

  #2020740

  Sue_H - Thursday, January 31, 2019 9:45 AM

  I mixed them up when I posted and didn't make it clear enough on the truncation.
  I'm not sure it really matters if trying for the minimum - the storage is the same with precision 1-9. Which is why I would have just left it.

  Sue

  Ok.  Thank you.

• Johnson Welch

  SSCoach

  Points: 18773

  More actions

  February 1, 2019 at 10:54 pm

  #2020920

  The error comes when you are trying to convert numeric or decimal value into varchar data type and the length of this variable is not long enough to hold its decimal or numeric value. Make sure that the length of VARCHAR variable or column is long enough to hold the decimal or numeric value. Avoid this error by increasing the length of the VARCHAR variable