tsql - 避免 TSQL 数据转换错误

Question

我认为最好以简单示例的形式提出这个问题。以下 SQL 块会导致“DB-Library Error:20049 Severity:4 Message:Data-conversion导致溢出”消息，但怎么会呢？

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 

这与以下有何不同:

select 1.000000/(1.000000/1.000000)
go

哪个工作正常？

Answer 1

我上次尝试使用 Sybase 时遇到了同样的问题(很多年前)。出于 SQL Server 的心态，我没有意识到 Sybase 会尝试将小数强制输出——从数学上讲，这就是它 应该做。 :)

来自 Sybase manual :

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

再往下:

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.

因此，假设在您的场景中精度损失是可以接受的，您可能需要在交易开始时执行以下操作:

SET ARITHABORT NUMERIC_TRUNCATION OFF

然后在您的交易结束时:

SET ARITHABORT NUMERIC_TRUNCATION ON

这就是多年前为我解决的问题......