EAV结构下INNER JOIN执行异常：nvarchar值转int失败问题咨询

报错原因 #

• 核心是SQL Server的执行计划优化逻辑导致的谓词计算顺序问题：SQL的语法执行顺序不代表实际物理执行顺序，查询优化器为了提升执行效率，可能会优先扫描全表计算你写的转换逻辑（IIF(ISNUMERIC(FV.[Data]) = 1, FV.[Data] , 0)），之后再应用FieldID = 对应业务字段ID的过滤条件。这时候就会扫描到报价字段的'110.00'值，尝试把这个带小数点的字符串直接转int时就会触发报错，哪怕这行数据后续会被过滤掉、不会出现在最终结果里。
• 另外ISNUMERIC本身存在设计缺陷，只要是能转成任意数值类型（包括decimal、float）的字符串都会返回1，'110.00'的ISNUMERIC结果是1，但它不符合int的格式要求，所以直接转换必然失败。

加REPLACE后正常的原因 #

你用REPLACE(FV.[Data],'.' , '')把字符串里的小数点全部删掉了，'110.00'处理后变成了'11000'，属于合法的int格式字符串，转换过程不会报错，所以哪怕优化器提前做转换也不会触发异常。但这种方案属于临时规避，存在隐患：如果后续出现带其他非数字字符的字段值、或者金额字段有两位以上小数的情况，还是会触发报错。

更稳妥的修复方案 #

建议替换掉不稳定的ISNUMERIC+REPLACE组合，用SQL Server原生的安全转换函数：

-- 用TRY_CAST先转成decimal再转int，能兼容带小数点的合法数值字符串
SELECT R.RecordID RID,  R.CreatedOn CO,R.FormID ,               
(Select ServiceNameEn From InstakinServices where InstakinServiceID = ISNULL(TRY_CAST(CAST(FV.[Data] AS DECIMAL(18,2)) AS INT), 0)) [Service]
,(Select NameEn From Cities where CityID = ISNULL(TRY_CAST(CAST(FVC.[Data] AS DECIMAL(18,2)) AS INT), 0)) City
FROM Records R
INNER JOIN Forms F ON F.FormID = R.FormID
INNER JOIN FieldValues FV ON FV.RecordID = R.RecordID AND FV.FieldID = (SELECT FieldID FROM Fields WHERE FormID = R.FormID AND FieldName = 'SelectService')
INNER JOIN FieldValues FVC ON FVC.RecordID = R.RecordID AND FVC.FieldID = (SELECT FieldID FROM Fields WHERE FormID = R.FormID AND FieldName = 'SelectCity')

也可以提前用CTE把需要的两个业务字段的FieldValues行先过滤出来，再做转换，从逻辑上避免扫描到其他无关字段的取值：

WITH TargetFieldValues AS (
    SELECT 
        RecordID,
        FieldID,
        Data
    FROM FieldValues
    WHERE FieldID IN (
        SELECT FieldID FROM Fields WHERE FieldName IN ('SelectService','SelectCity')
    )
)
SELECT 
    R.RecordID RID,  
    R.CreatedOn CO,
    R.FormID,               
    (Select ServiceNameEn From InstakinServices where InstakinServiceID = ISNULL(TRY_CAST(CAST(FV.[Data] AS DECIMAL(18,2)) AS INT), 0)) [Service],
    (Select NameEn From Cities where CityID = ISNULL(TRY_CAST(CAST(FVC.[Data] AS DECIMAL(18,2)) AS INT), 0)) City
FROM Records R
INNER JOIN Forms F ON F.FormID = R.FormID
INNER JOIN TargetFieldValues FV ON FV.RecordID = R.RecordID AND FV.FieldID = (SELECT FieldID FROM Fields WHERE FormID = R.FormID AND FieldName = 'SelectService')
INNER JOIN TargetFieldValues FVC ON FVC.RecordID = R.RecordID AND FVC.FieldID = (SELECT FieldID FROM Fields WHERE FormID = R.FormID AND FieldName = 'SelectCity')

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