SQL Server 2017评估了一段永远不应该评估的代码

Question

select * 
from fb_lab_test 
where (report_item_code = 'HBcAb') 
or (report_item_code = 'Anti-Hbc' and 
case isnumeric(result) when 1 then cast(result as float) else 10000.0 end > 0.2)

将数据类型varchar转换为浮动时出错

这是的样本数据

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE fb_lab_test
(
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Test_No] [varchar](50) NULL,
[execute_date] [datetime] NULL,
[PatientId] [varchar](20) NULL,
[Visit_Id] [varchar](10) NULL,
[Patient_Type] [int] NULL,
[PatientName] [varchar](100) NULL,
[result_date_time] [datetime] NULL,
[report_item_name] [varchar](256) NULL,
[report_item_code] [varchar](50) NULL,
[result] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT INTO fb_lab_test 
VALUES ('5910315197','2019-10-31 00:40:53.000','111111','1','1','Tom','2019-10-31 08:56:54.000','test1','KET','-')

在这个样本数据中，isnumeric会返回假阳性，但case isnumeric(result) when 1 then cast(result as float) else 10000.0 end > 0.2永远不应该被评估，因为在样本日期中没有名为"Anti-Hbc"的report_item_code，这很奇怪。

Answer 1

在我的情况下(使用您的数据(不会引发错误。有时，引擎决定提前执行操作(以便优化特定查询(。我相信，你会因为这种行为而犯错。

您可以使用TRY_CAST:进行修复

select * from fb_lab_test 
where (report_item_code = 'HBcAb') 
or (
report_item_code = 'Anti-Hbc' and 
case isnumeric(result) when 1 then try_cast(result as float) else 10000.0 end > 0.2
)

Answer 2

它实际上是按照文档执行的。。。

对于某些非数字字符(如加号(+(、减号(-(和有效货币符号(如美元符号($((，ISNUMERIC返回1。有关货币符号的完整列表，请参阅money和smallmoney(Transact-SQL(。

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-服务器-ver15

SQL Server在将非数字char、nchar、nvarchar或varchar数据转换为decimal、float、int或numeric时返回错误消息。当空字符串("(转换为数字或十进制时，SQL Server也会返回错误。

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-服务器-ver15

如果你需要将其转换为一个表达式，我建议你使用最多支持4位小数的货币类型。否则，您必须处理+-.等的"可能"数值…

Answer 3

根本不要使用isnumeric()。只需使用try_函数：

select t.* 
from fb_lab_test t
where report_item_code = 'HBcAb'  or
(report_item_code = 'Anti-Hbc' and 
coalesce(try_cast(float_result), 10000.0) > 0.2
);

在where子句中避免使用case表达式也是一个好主意。它们基本上会使优化器短路。

在这种情况下，您希望包含非数字值。更常见的情况是，这些将被排除在外。您可以让NULL比较排除它们。这种逻辑是：

select t.* 
from fb_lab_test t
where report_item_code = 'HBcAb'  or
(report_item_code = 'Anti-Hbc' and 
try_cast(float_result) > 0.2   -- excludes non-NULL values
);