Arithmetic overflow error converting numeric to data type numeric.

Question

Post reply

Arithmetic overflow error converting numeric to data type numeric.

Cees Cappelle-442904

SSC Eights!

Points: 917
More actions
November 28, 2008 at 4:40 am

#126170

Here's my code:
DECLARE @BrutoPrijs Decimal(16,9)
SET @BrutoPrijs = 100000000.0000
PRINT LEN(CONVERT(VarChar(Max),100000000.0000))
I get:
Arithmetic overflow error converting numeric to data type numeric.
Why? The Len statement shows it's only 14 char long.
And in bol:
Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
What am I missing????
Cees Cappelle

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

Decimal(16,9) allows 7 digits to the left of the decimal place, your number has 9, so

SET @BrutoPrijs = 100000000.0000

will fail

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

of the 16 values in your numeric number 9 are keep for the right hand side of the decimal, meaning that only 7 digits can be used on the right of the decimal.

If you changed you code to this it would work.

DECLARE @BrutoPrijs NUMERIC(23,9)

SET @BrutoPrijs = 100000000.0000

PRINT LEN(CONVERT(VarChar(Max),100000000.0000))

GO

DECLARE @BrutoPrijs NUMERIC(16,4)

SET @BrutoPrijs = 100000000.0000

PRINT LEN(CONVERT(VarChar(Max),100000000.0000))

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Cees Cappelle-442904 SSC Eights! Points: 917 More actions · Answer 3

Ok, that's a lot clearer for me then.

How do I check the size before processing?

Is there a way to handle this error in my User defined function, zo that the Stored Procedure doesn't fail. I know I can't use try...catch in a udf, but what else is available?

Cees Cappelle