|
|
Error Message:
Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type
numeric.
Causes:
This error is usually encountered with decimal or numeric data types wherein the
precision of the column or variable is not enough to hold the value being
assigned to it.
|
The simplest way reproduce the error is with the following script:
DECLARE @DecimalValue DECIMAL(4,2)
SET @DecimalValue = 100.00
Since the precision of the @DecimalValue variable is set to 4 with 2 decimal
places, the highest value it can have is 99.99. Assigning a value that
has more than 2 digits on the left of the decimal place, as the example shows,
generates the error:
Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
On the other hand, if you assign a value where there are more decimal places
than what the variable can hold, it simply rounds the value and does not
generate any error:
DECLARE @DecimalValue DECIMAL(4,2)
SET @DecimalValue = 98.4584
SELECT @DecimalValue -- Returns 98.46
Solution/Workaround:
To avoid this error, always make sure that the precision of the decimal or
numeric variable that you are going to use is enough to accommodate the value
being assigned to it. Just by increasing the precision, the error can be
avoided:
DECLARE @DecimalValue DECIMAL(5,2)
SET @DecimalValue = 100.00
|