Avoiding TSQL Data-conversion errors

Question

I think this is best asked in the form of a simple example. The following chunk of SQL causes a "DB-Library Error:20049 Severity:4 Message:Data-conversion resulted in overflow" message, but how come?

declare @a numeric(18,6), @b numeric(18,6), @c numeric(18,6)
select @a = 1.000000, @b = 1.000000, @c = 1.000000
select @a/(@b/@c)
go

How is this any different to:

select 1.000000/(1.000000/1.000000)
go

which works fine?

Answer 1

Which database are you using? I just ran this against SQL Server 2008 and it worked fine.

Answer 2

This is just speculation, but could it be that the DBMS doesn't look at the dynamic value of your variables but only the potential values? Thus, a six-decimal numeric divided by a six-decimal numeric could result in a twelve-decimal numeric; in the literal division, the DBMS knows there is no overflow. Still not sure why the DBMS would care, though--shouldn't it return the result of two six-decimal divisions as up to a 18-decimal numeric?

Answer 3

Because you have declared the variables in the first example the result is expected to be of the same declaration (i.e. numeric (18,6)) but it is not.

I have to say that the first one worked in SQL2005 though (returned 1.000000 [The same declared type]) while the second one returned (1.00000000000000000000000 [A total different declaration]).

Answer 4

I ran into the same problem the last time I tried to use Sybase (many years ago). Coming from a SQL Server mindset, I didn't realize that Sybase would attempt to coerce the decimals out -- which, mathematically, is what it should do. :)

From the Sybase manual:

Arithmetic overflow errors occur when the new type has too few decimal places to accommodate the results.

And further down:

During implicit conversions to numeric or decimal types, loss of scale generates a scale error. Use the arithabort numeric_truncation option to determine how serious such an error is considered. The default setting, arithabort numeric_truncation on, aborts the statement that causes the error but continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing.

So assuming that the loss of precision is acceptable in your scenario, you probably want the following at the beginning of your transaction:

SET ARITHABORT NUMERIC_TRUNCATION OFF

And then at the end of your transaction:

SET ARITHABORT NUMERIC_TRUNCATION ON

This is what solved it for me those many years ago ...