SQL存储过程中INT类型值动态IN子句转换报错解决问题

IN运算符预期接收的是值列表，而非单个逗号拼接的字符串。现有代码中@transactIds是varchar类型的拼接字符串，SQL执行时会尝试将整个字符串转为int类型和transactionTypeID对比，因此触发类型转换报错。
现有方案中临时表性能差，大概率是未建立索引、统计信息不匹配导致执行计划退化，下面的方案都可以在不修改原有IF分支逻辑的前提下修复问题，性能接近硬编码水平。

方案1：STRING_SPLIT拆分（SQL Server 2016+ 首选，性能最优） #

使用内置STRING_SPLIT函数拆分字符串，额外开销极低，支持正常命中transactionTypeID列的索引：

SELECT TOP 10 * 
FROM Transactions  
WHERE transactionTypeID IN (
    SELECT CAST(value AS INT) 
    FROM STRING_SPLIT(@transactIds, ',')
)

方案2：XML拆分（兼容SQL Server 2008及以上版本） #

如果数据库版本低于2016，可以用XML方式拆分，性能远高于普通临时表方案：

SELECT TOP 10 * 
FROM Transactions  
WHERE transactionTypeID IN (
    SELECT CAST(t.c.value('.', 'INT') AS INT)
    FROM (
        SELECT CAST('<x>' + REPLACE(@transactIds, ',', '</x><x>') + '</x>' AS XML) AS v
    ) AS x
    CROSS APPLY x.v.nodes('/x') t(c)
)

方案3：动态SQL执行（性能和硬编码完全一致） #

如果希望完全复用原IN子句的写法，可使用动态SQL执行，参数都是内部拼接的数字，不存在SQL注入风险：

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT TOP 10 * 
FROM Transactions  
WHERE transactionTypeID IN (' + @transactIds + N')'
EXEC sp_executesql @sql

如果坚持使用临时表方案，可以在临时表的ID列建立非聚集索引，同时查询时添加OPTION (RECOMPILE)让优化器感知临时表的数据分布，执行速度会大幅提升，接近硬编码水平。

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