SQL Server中如何根据ERP产品参考值内容动态转换数据类型

问题原因 #

你遇到的报错源于SQL Server对CASE表达式的两个核心处理规则：

1. 数据类型一致性要求：CASE表达式的所有分支必须返回兼容的数据类型，SQL会按照数据类型优先级自动做隐式转换。int类型优先级高于varchar，所以即使你的WHEN分支返回字符串，SQL也会尝试把它转成int，而带'CN'的字符串无法完成这个转换，直接报错。
2. 表达式预计算：SQL在执行时会先计算CASE所有分支的表达式，再应用条件判断。也就是说，哪怕某行满足LIKE '%CN%'的条件，ELSE分支里的CAST(INVMB.MB110 AS INT)也会被执行，导致转换失败。

解决方案 #

由于SQL的单列只能存储一种数据类型，无法实现同一列同时存在int和varchar类型，你可以根据需求选择以下方案：

方案1：拆分两列分别存储文本和数值 #

用两个列分别保留原始文本和转换后的数值（带CN的行数值列为NULL），这样既能保留原始信息，又能拿到纯数字的数值类型：

SELECT
    INVMB.MB110 AS Reference_Text,
    CASE 
        WHEN INVMB.MB110 NOT LIKE '%CN%' 
        THEN TRY_CAST(INVMB.MB110 AS INT) 
        ELSE NULL 
    END AS Reference_Numeric
FROM INVMB

TRY_CAST是SQL Server 2012及以上版本支持的函数，它会在转换失败时返回NULL，避免报错。

方案2：统一返回字符串类型 #

如果不需要数值类型，只是想区分显示，直接返回原始字符串即可（纯数字的行自然是数字格式的字符串）：

SELECT INVMB.MB110 AS Reference
FROM INVMB

如果需要确保纯数字的行格式统一，可以显式转换为字符串，去除可能的前导空格：

SELECT
    CASE 
        WHEN INVMB.MB110 LIKE '%CN%' 
        THEN INVMB.MB110 
        ELSE CAST(CAST(INVMB.MB110 AS INT) AS VARCHAR(50)) 
    END AS Reference
FROM INVMB

内容的提问来源于stack exchange，提问作者Luc-Olivier Pineau