Arithmetic overflow error converting numeric to data type numeric.

Question

Declare @Number Numeric(5,2)=0.2500
Declare @pagebreak INT =500
Declare @result Numeric(5,2)

Select @result=  @pagebreak/@Number

I increased the @result datatype value , still i am getting error

Msg 8115, Level 16, State 8, Line 6

Arithmetic overflow error converting numeric to data type numeric.

Answer 1

Because SQL Server tries to convert the highest data type precedence (numeric)

select 500/0.2500

select cast(2000.000000 as decimal(5,2))-error

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Marimuthu,

As per your declaration for result variable, oly 3 digits available for result before the decimal point while the answer for the calculation is 4 digits long (2000 s the answer)

Hence modify the declaration for result:

declare @result numeric(7,2) 
-- means, 2 digits after decimal point(scale) 
--and 7-2=5 digits before decimal point..

This shud solve the problem..

---

Thanks,
JK

<Please mark as 'answered' if this answers your query> <Please mark as 'helpful' if this was helpful to your query>

Answer 3

To workaround

Declare @Number decimal(5,2)=0.2500
Declare @pagebreak INT =500
Declare @result decimal(18,3)
Select @result=  @pagebreak/@Number 
select @result

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Declare @Number Numeric(5,2)=0.2500
Declare @pagebreak INT =500
Declare @result Numeric(5,2)

Select @result=  @pagebreak/@Number

I increased the @result datatype value , still i am getting error

Msg 8115, Level 16, State 8, Line 6

Arithmetic overflow error converting numeric to data type numeric.

The result of this calculation is 2000 but the @result variable is only numeric(5,2), allowing a max value of 999.99.  You need to increase to (at least) numeric(6,2).

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com