SQL Server带WHERE条件的查询触发非数值转换错误，求原因及解决

问题重现 #

以下是测试用的SQL代码：

create table A 
(
    [CODEID] [int] NOT NULL,
    [PRJID] [int] not null,
    [CODEVALUE] [varchar](200) NULL
)

create table B 
(
    [CODEID] [int] NOT NULL,
    [CODENAME] [varchar](100)
)
go

insert into A 
select 1,99,'ABC'
union
select 2,99,'5-0-0'
go

insert into B
select 1, 'ONE'
union
select 2, 'TWO'

;with dt as 
(
    select cast(replace(CODEVALUE,'-','') as int) VAL 
    from A
    join B on B.CODEID = A.CODEID and B.CODENAME = 'TWO' and A.PRJID = 99
)
select MSG 
from 
    (select 
         case when VAL>25 then 'BRAVO' end MSG 
     from dt) q
where 
    MSG is not null

预期该查询能返回MSG字段，但SQL Server却尝试将'ABC'转换为int类型引发错误；移除WHERE MSG IS NOT NULL条件后，查询可正常运行。

问题原因 #

SQL Server的查询优化器会根据成本估算调整执行顺序，不会严格遵循代码的书写逻辑顺序。虽然你的JOIN条件理论上只会筛选出A表中CODEID=2（对应B表的'TWO'）的行，但优化器可能优先执行cast(replace(CODEVALUE,'-','') as int)转换操作，再应用JOIN和过滤条件。这就导致A表中CODEID=1的'ABC'被错误地拿去做int转换，触发类型转换异常。

当移除WHERE MSG IS NOT NULL时，优化器生成的执行计划发生了变化，可能先完成了JOIN过滤再执行转换，因此没有报错，但这种行为是不可靠的——优化器的执行计划会随数据量、索引、统计信息等因素变化，随时可能再次触发错误。

解决方法 #

方法1：使用安全转换函数TRY_CAST（推荐，SQL Server 2012+支持） #

TRY_CAST在转换失败时会返回NULL而非抛出错误，从根源上避免了转换异常。修改CTE的转换逻辑即可：

;with dt as 
(
    select TRY_CAST(replace(CODEVALUE,'-','') as int) VAL 
    from A
    join B on B.CODEID = A.CODEID and B.CODENAME = 'TWO' and A.PRJID = 99
)
select MSG 
from 
    (select 
         case when VAL>25 then 'BRAVO' end MSG 
     from dt) q
where 
    MSG is not null

方法2：通过CASE语句控制转换时机 #

确保仅在符合过滤条件的行上执行转换，避免无效数据进入转换步骤：

;with dt as 
(
    select 
        case 
            -- 先确认当前行是目标数据，再执行转换
            when B.CODENAME = 'TWO' and A.PRJID = 99 
            then cast(replace(CODEVALUE,'-','') as int) 
        end VAL 
    from A
    join B on B.CODEID = A.CODEID
)
select MSG 
from 
    (select 
         case when VAL>25 then 'BRAVO' end MSG 
     from dt) q
where 
    MSG is not null

这两种方法都能保证无论优化器如何调整执行顺序，都不会出现无效数据被转换的情况，彻底解决问题。

内容的提问来源于stack exchange，提问作者Radu B.