Arithmetic overflow error converting numeric to data type numeric

 Arithmetic OverFlow:  It occur when  we try to insert a value in a column or assign a value to a variable  which is more than the limit of data type.

Let' say we have a variable of numeric type with size

            Declare @IsArithmeticOverFlow numeric(5,2)

            SET @IsArithmeticOverFlow  = 999.999

            SELECT @IsArithmeticOverFlow 

will get the airthmetic error  error here while executing the above statement because we defined the size of variable (5,2) and IsArithmeticOverFlow  vaiable have the capacity to hold the value upto only 999, not more than it. but the value 999.999 is rounding to nearest vale. so, after rounding, the nearest vale is 1000. and the variable don't have the capacity now to hold 1000. so in this situation, will get arithmetic overflow error

Now let's fix this issue. I am just increasing the size of variable from (5,2) to (6,2)

Declare @IsArithmeticOverFlow numeric(6,2)

            SET @IsArithmeticOverFlow  = 999.999

            SELECT @IsArithmeticOverFlow 

Let's run it and see the output.

Now you can see the above screenshot, no error after increasing the size of variable.

Let say if someone is using more than one nested procedure inside a procedure and getting arithmetic overflow issue then use the try catch to identify the eject place where it is occurring.

We'll discuss in details about try catch in our next post.