sql - SQL:Atithmeic 溢出错误，casting 和 try cast 没有解决问题

Question

我目前正在研究大量更新变量的解决方案。但是我遇到了一个问题。执行查询时出现以下错误：

将数字转换为数据类型数字的算术溢出错误

查询：

update Table_Name 
SET Percentage = CAST((((tn.variable_1 - tn.variable_2 ) / (tn.variable_3 - tn.variable_2 ))  * 100.0) AS numeric(14, 4))
FROM Table_Name tn
WHERE Percentage IS NULL
AND tn.variable_1 is not null
AND tn.variable_2 is not null
AND tn.variable_3 is not null
GO

百分比列定义为大小为 14 且逗号 (,) 后大小为 4 的小数。这意味着它被定义为小数百分比 (14,4)。我尝试将其转换为十进制（14,4），但这似乎并没有影响到它。

我尝试过的变体：1

update Table_Name 
SET Percentage = TRY_CAST((((tn.variable_1 - tn.variable_2 ) / (tn.variable_3 - tn.variable_2 ))  * 100.0) AS numeric(14, 4))
FROM Table_Name tn
WHERE Percentage IS NULL
AND tn.variable_1 is not null
AND tn.variable_2 is not null
AND tn.variable_3 is not null
GO

2

update Table_Name 
SET Percentage = TRY_CAST((TRY_CAST(((tn.variable_1 - tn.variable_2 ) / (tn.variable_3 - tn.variable_2 )))  * 100.0) AS numeric(14, 4))
FROM Table_Name tn
WHERE Percentage IS NULL
AND tn.variable_1 is not null
AND tn.variable_2 is not null
AND tn.variable_3 is not null
GO

我知道第一个版本适用于特定数据集，但由于上述错误，它不会在实时环境中更新。我有点不知所措。

标签： sqlsql-servercastingoverflownumeric