sql - 在 where 子句中使用小数 - 将 nvarchar 转换为数字数据类型时出现算术溢出错误

Question

我遇到一个 sql server 错误，但不确定如何修复它。我在 View “Products”中得到一个类型为 nvarchar(30) 的列“NAME”，生成了查询在代码中是动态的，所以不能完全改变它。

我收到“将 nvarchar 转换为数字数据类型的算术溢出错误”。对于以下查询:

select * FROM Products WHERE  NAME=12.0

但是下面的查询工作正常:

select * FROM Products WHERE  NAME=112.0

我对这个错误感到很困惑，我知道我应该在数字周围加上引号，但只想知道为什么第二个查询有效，是否有任何设置可以使第一个查询有效？

更新:还有

select * FROM Products WHERE  NAME=cast('12.0' as decimal(4,2))

没用，但是

select * FROM Products WHERE  NAME=cast('12.0' as decimal(5,2)) 

有效，有什么特别的原因吗？

非常感谢!

Answer 1

SQL Server 正在尝试转换表中的值以匹配编码到 WHERE 子句中的值的感知数据类型。如果您有包含更多数字的数据值(例如，DECIMAL(5,2))，并且您尝试将它们转换为匹配数字更少的值(例如，DECIMAL(3,1))，那么你就会溢出。

考虑以下 SQL，它会抛出一个错误:

DECLARE @Products TABLE (NAME NVARCHAR(30))
INSERT INTO @Products VALUES ('123.45')
INSERT INTO @Products VALUES ('12.0')

SELECT *
FROM @Products
WHERE NAME = 12.0

现在试试这个，它会起作用:

DECLARE @Products TABLE (NAME NVARCHAR(30))
INSERT INTO @Products VALUES ('123.45')
INSERT INTO @Products VALUES ('12.0')

SELECT *
FROM @Products
WHERE NAME = CAST(12.0 AS DECIMAL(5,2))

它们之间的区别在于，SQL Server 现在考虑到表包含比 WHERE 子句中指定的数字具有更高精度和/或小数位数的情况。

编辑:further reading .联机丛书在 DECIMAL 和 NUMERIC 的数据类型定义中指出:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

因此，当您使用常量“12.0”发出查询时，它会被转换为数据类型 NUMERIC(3,1)，然后尝试转换 NVARCHAR 值。