将varchar转换为数值数据类型的算术溢出错误-找不到合适的答案

这里有两个问题，第一个是仅仅因为某些事物通过，ISNUMERIC并不意味着您可以将其转换为小数。以下将返回1：

SELECT  ISNUMERIC('1,0');

但这将返回错误：

SELECT CAST('1,0' AS DECIMAL(3, 1))

另一个不太明显的问题是您无法控制SQL Server何时执行哪个操作，因此即使您ISNUMERIC(resultValue) = 1在第一个CTE中进行控制，也无法保证此操作将在之前执行WHERE CAST(ResultValue AS decimal(3,1)) BETWEEN 7.5 AND 10。因此，您仍可能会尝试（但失败）将非数字值转换为小数。它在中时有效，SELECT因为它是在之后进行评估的WHERE。

有两种解决方法，后一种是hack。解决该问题的最佳方法是通过使用临时表来强制第一个查询的实现：

CREATE TABLE #ResultsComponent
(
    [RequisitionNumber] VARCHAR(50),
    [ResultsName] VARCHAR(50),
    ResultValue DECIMAL(3, 1)
);
INSERT #ResultsComponent (RequisitionNumber, ResultsName, ResultValue)
SELECT [RequisitionNumber]
      ,[ResultsName]
      ,LTRIM(RTRIM([ResultValue]))
FROM dbo.RESULTS_TEST_COMPONENT
--WHERE TestGroupNumber IN ('T4367', 'T8033')
WHERE SUBSTRING(RequisitionNumber, 2, 4) = '1521'
AND ResultsName = 'HEMOGLOBIN A1C'
AND ISNUMERIC(ResultValue) = 1;

SELECT  RequisitionNumber, ResultsName, ResultValue
FROM    #ResultsComponent
WHERE   ResultValue BETWEEN 7.5 AND 10;

另一种方法是hack，不能保证能正常工作，它是TOP用来强制执行顺序的，但是要确保选择的值足够高，以免影响结果：

WITH RESULTS_TEST_COMPONENT_CTE(RequisitionNumber, ResultsName, ResultValue)
AS
(
SELECT TOP (100000)
        [RequisitionNumber]
      ,[ResultsName]
      ,LTRIM(RTRIM([ResultValue]))
FROM dbo.RESULTS_TEST_COMPONENT
--WHERE TestGroupNumber IN ('T4367', 'T8033')
WHERE SUBSTRING(RequisitionNumber, 2, 4) = '1521'
AND ResultsName = 'HEMOGLOBIN A1C'
AND ISNUMERIC(ResultValue) = 1
)
SELECT RequisitionNumber
      ,ResultsName
      ,CAST(ResultValue AS decimal(3,1)) AS ResultValue
FROM RESULTS_TEST_COMPONENT_CTE
WHERE CAST(ResultValue AS decimal(3,1)) BETWEEN 7.5 AND 10;

我们可以很简单地重现问题：

CREATE TABLE #T (ResultValue VARCHAR(100))
INSERT #T (ResultValue)
SELECT  t.ResultValue
FROM    sys.all_objects o
        CROSS APPLY (VALUES (CAST(Name AS VARCHAR(100))), (CAST(object_id AS VARCHAR(100)))) t (ResultValue);

WITH CTE AS
(   SELECT  ResultValue = CAST(ResultValue AS INT)
    FROM    #T
    WHERE   ISNUMERIC(ResultValue) = 1
)
SELECT  *
FROM    CTE 
WHERE   ResultValue BETWEEN 1 AND 10;

然后查看估计的计划（由于错误而无法获得实际计划），并看到两个谓词（ISNUMERIC和BETWEEN 1 AND 10）同时被评估：

通过添加top，我们可以更改计划，以便初始表扫描筛选器针对ISNUMERIC，然后使用其他步骤来确保BETWEEN 1 AND 10仅将其应用于数字数据：

WITH CTE AS
(   SELECT  TOP (1000000) ResultValue = CAST(ResultValue AS INT)
    FROM    #T
    WHERE   ISNUMERIC(ResultValue) = 1
)
SELECT  *
FROM    CTE 
WHERE   ResultValue BETWEEN 1 AND 10;