IN子句动态转换值失效：SQL查询硬编码有效但动态处理无效

这是个非常常见的SQL使用误区，我来帮你理清问题根源并给出可行的解决方案：

问题原因 #

你通过REPLACE把原始字符串'Canada,Portugal'转换成了'Canada','Portugal'，但当你把这个变量直接放到IN子句里时，SQL Server会把整个变量值当作单一的字符串参数，而不是解析成两个独立的条件值。

简单说，你的代码实际执行的逻辑是去匹配等于'Canada','Portugal'这个完整字符串的记录，而不是匹配Canada或者Portugal——这就是查询失效的核心原因。

解决方案 #

根据你的SQL Server版本，推荐以下几种方案：

1. 使用内置字符串分割函数（SQL Server 2016+） #

如果你的SQL Server版本是2016及以上，直接用STRING_SPLIT函数分割原始逗号字符串，不需要手动拼接引号：

declare @GeographicalLocation varchar(max) = 'Canada,Portugal';

select ContinentName 
from [ContinentList] 
where ContinentId in (
    select ContinentId 
    from [CountryList] 
    where CountryName IN (SELECT value FROM STRING_SPLIT(@GeographicalLocation, ','))
);

这个方法最安全、高效，还能避免SQL注入风险，是首选方案。

2. 动态SQL（适用于旧版本，谨慎使用） #

如果是SQL Server 2016之前的版本，可以用动态SQL把拼接好的字符串作为SQL语句的一部分执行，但要注意SQL注入风险（如果变量来自用户输入，一定要做严格的输入验证）：

declare @GeographicalLocation varchar(max) = 'Canada,Portugal';
declare @SQL nvarchar(max);

-- 先拼接成带引号的格式
set @GeographicalLocation = REPLACE(@GeographicalLocation, ',', ''',''');
set @GeographicalLocation = '''' + @GeographicalLocation + '''';

-- 构建动态SQL语句
set @SQL = N'
select ContinentName 
from [ContinentList] 
where ContinentId in (
    select ContinentId 
    from [CountryList] 
    where CountryName IN (' + @GeographicalLocation + ')
)';

-- 执行动态SQL
EXEC sp_executesql @SQL;

3. 自定义表值分割函数（旧版本替代方案） #

如果无法使用STRING_SPLIT，可以自己写一个表值函数来分割字符串：

CREATE FUNCTION dbo.SplitString (@InputString varchar(max), @Delimiter char(1))
RETURNS @OutputTable TABLE (value varchar(max))
AS
BEGIN
    DECLARE @StartIndex int = 1, @EndIndex int;
    
    -- 确保字符串末尾有分隔符，避免遗漏最后一个值
    IF RIGHT(@InputString, 1) <> @Delimiter
        SET @InputString = @InputString + @Delimiter;

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString);
        INSERT INTO @OutputTable(value)
        SELECT SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex);
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString));
    END;
    RETURN;
END;

然后在查询中调用这个函数：

declare @GeographicalLocation varchar(max) = 'Canada,Portugal';

select ContinentName 
from [ContinentList] 
where ContinentId in (
    select ContinentId 
    from [CountryList] 
    where CountryName IN (SELECT value FROM dbo.SplitString(@GeographicalLocation, ','))
);

总结 #

优先推荐使用STRING_SPLIT（版本允许的话），它是官方支持的高效方案；动态SQL尽量只在必要时使用，且必须做好输入安全校验；自定义分割函数则是旧版本的稳妥替代。

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