ETL过程中SQL Server存储过程出现'Arithmetic overflow error converting numeric to data type numeric'错误，如何定位引发错误的数值？

我之前做ETL递归计算时也碰到过一模一样的算术溢出问题——哪怕调高decimal精度也没解决，毕竟递归乘法的数值增长速度经常超出预期。下面几个方法能帮你精准定位引发错误的具体数值：

1. 限制递归层数，逐步排查 #

递归CTE的溢出通常发生在某一层的乘法结果突破了decimal的存储上限。你可以通过MAXRECURSION选项限制递归次数，从低到高逐步测试，找到第一次出现溢出的层级，再查看该层级的源数据：

WITH CTE (qty_top, qty_lvl, recursion_level) AS (
    -- 初始层：加入递归层级标记
    SELECT 
        CONVERT(decimal(30,6), t.unity), 
        CONVERT(decimal(30,6), t.unity),
        1 AS recursion_level
    FROM top_tab
    UNION ALL
    -- 递归层：补全你遗漏的JOIN条件
    SELECT 
        CONVERT(decimal(30,6), cte.qty_top * t.unity), 
        CONVERT(decimal(30,6), nom.Nomenclature_unite_production),
        recursion_level + 1
    FROM CTE cte
    JOIN tab t ON -- 这里补上你的关联条件（原代码遗漏了）
    JOIN nom ON -- 补上nom表的关联条件
)
SELECT * FROM CTE 
OPTION (MAXRECURSION 3); -- 先测试前3层，逐步增加直到触发错误

当你找到触发错误的层级后，就可以单独查询该层级的输入值，看看是哪条记录的qty_top乘以t.unity后超出了范围。

2. 用TRY_CONVERT捕获溢出记录 #

直接在查询中使用TRY_CONVERT替代CONVERT，这样转换失败（溢出）时会返回NULL，你就能筛选出这些异常记录：

WITH CTE (qty_top, qty_lvl) AS (
    SELECT 
        CONVERT(decimal(30,6), t.unity), 
        CONVERT(decimal(30,6), t.unity)
    FROM top_tab
    UNION ALL
    SELECT 
        TRY_CONVERT(decimal(30,6), cte.qty_top * t.unity), 
        CONVERT(decimal(30,6), nom.Nomenclature_unite_production)
    FROM CTE cte
    JOIN tab t ON -- 补全关联条件
    JOIN nom ON -- 补全关联条件
)
-- 找出转换失败的记录，以及对应的源数值
SELECT 
    cte.qty_top AS previous_calculated_value,
    t.unity AS multiplied_value,
    nom.Nomenclature_unite_production AS production_unit
FROM CTE cte
JOIN tab t ON -- 补全关联条件
JOIN nom ON -- 补全关联条件
WHERE cte.qty_top IS NULL;

这个方法能直接定位到哪一次乘法操作导致了溢出，以及参与计算的两个数值。

3. 检查decimal的实际存储上限 #

别忘了，decimal(30,6)的总位数是30位，其中6位是小数，所以整数部分最多能存24位数字（也就是最大到999999999999999999999999.999999）。如果你的递归乘法结果超过了这个值，哪怕调到decimal(38,6)也有上限（整数部分32位）。

如果是这种情况，你可能需要重新设计计算逻辑：比如用对数存储中间结果（最后再转换回来）、拆分计算步骤，或者如果精度要求不高的话，考虑用float类型（但要注意float的精度损失问题）。

另外提一句：你的原代码里递归部分的FROM CTE_NOM cte应该是笔误吧？应该是FROM CTE cte，而且JOIN语句缺少关联条件，这可能导致笛卡尔积，产生大量超大数值，也是溢出的潜在原因，记得补全哦。

内容的提问来源于stack exchange，提问作者Louis Chopard