I am experiencing an error message when I run a TSQL and I don't know how to
correct it.

Server: Msg 8115, Level 16, State 8, Line 38
Arithmetic overflow error converting numeric to data type numeric.

In this code I am extracting hierarchical information. I am going 6 levels
deep. The statement that is giving me the problem is for example at level
3.
Select @qty3=@qty2*@qty3

@qty2 is the Qty from the parent level.

The mystery is that it does not always return an error. And to add to the
mystery my information that is being pulled seems perfectly correct.

Any ideas?

arnie rowland

The product of ( @qty2 * @qty3 ) is too large to fit in the defined size of
@qty3.

Increase the scale of @qty3.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

aaron bertrand [sql server mvp]

Can you show the variable declarations, and sample data that causes the
problem?

I can reproduce easily with:


DECLARE
@qty2 NUMERIC(5,3),
@qty3 NUMERIC(5,3);

SET @qty2 = 10.001;
SET @qty3 = 10.002;

SET @qty3 = @qty2 * @qty3;

Followups set to microsoft.public.sqlserver.programming only. Not sure what
this has to do with clients/connect/server.

hari prasad

Hi,

This error is something to do with your variable declaration or column width.
So for few values you wont get error.

See the below example:-

select cast(round(10000000.125023343432,2) as decimal(6,2))

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
You need at least a decimal(11,2) for that value. The largest value that a
decimal(8,2) can represent is 9999.99 .

select cast(round(10000000.125023343432,2) as decimal(10,2))

Thanks
Hari
SQL Server MVP