SQL Server存储过程CONTAINS全文检索如何嵌入带通配符的变量

核心问题说明 #

使用CONTAINS方法编写存储过程时存在两类典型错误：

• 直接在检索条件字符串内写入'"[@Text]*"'不会解析@Text变量，SQL会将[@Text]作为固定检索文本执行，无法匹配动态传入的关键词
• C#侧构造带双引号的传参时转义逻辑错误，无法生成符合CONTAINS语法要求的"关键词*"前缀匹配格式

原有错误代码 #

错误存储过程 #

ALTER procedure [dbo].[SelectUsers_SP]
@Text nvarchar(50) = null
as 
begin 
        if (@Text is not null)
            begin
            -- 错误：字符串常量内不会解析变量
            select * from Users where contains(*, '"[@Text]*"') and IsDeleted = 0
            end
            
    select * 
    from Users 
    where IsDeleted = 0 
    return 0;
end

错误C#传参代码 #

// 错误：转义逻辑混乱，插值字符串内双引号未正确转义
_service.Select($""{text}*"")

推荐实现方案 #

方案1：存储过程内拼接检索条件（优先选用） #

直接在存储过程内完成检索条件格式拼接，避免应用层重复转义，同时统一处理特殊字符，稳定性更高。
修正后的存储过程代码：

ALTER procedure [dbo].[SelectUsers_SP]
@Text nvarchar(50) = null
as 
begin 
    if (@Text is not null)
    begin
        declare @SearchCondition nvarchar(100);
        -- 转义用户输入中的双引号，符合全文检索语法规则
        set @Text = replace(@Text, '"', '""');
        -- 拼接为要求的格式：前后包裹双引号，末尾追加*通配符
        set @SearchCondition = concat('"', @Text, '*"');
        
        select * from Users where contains(*, @SearchCondition) and IsDeleted = 0;
    end
    else
    begin
        select * from Users where IsDeleted = 0;
    end
    return 0;
end

实现说明：

• 必须转义用户输入的双引号，否则输入携带双引号时会直接触发CONTAINS语法错误
• 该写法无SQL注入风险：拼接结果仅作为CONTAINS函数的参数传入，不会被解析为SQL语句执行，不需要额外做注入防护
• 该方案下C#侧无需做任何格式处理，直接将用户输入的原始关键词传给存储过程@Text参数即可

方案2：C#侧构造完整检索条件传入 #

如果需要在应用层控制检索格式，可在C#侧完成字符串拼接和转义后，直接传给存储过程使用。
修正后的C#传参代码：

// 先转义输入中的双引号
string escapedText = text.Replace("\"", "\"\"");
// 拼接为CONTAINS要求的 "关键词*" 格式
string searchParam = $"\"{escapedText}*\"";
// 调用存储过程
_service.Select(searchParam);

对应简化后的存储过程代码：

ALTER procedure [dbo].[SelectUsers_SP]
@SearchCondition nvarchar(100) = null
as 
begin 
    if (@SearchCondition is not null)
    begin
        select * from Users where contains(*, @SearchCondition) and IsDeleted = 0;
    end
    else
    begin
        select * from Users where IsDeleted = 0;
    end
    return 0;
end

替代实现方案 #

如果表数据量较小，不需要全文检索的高性能特性，也可以使用LIKE实现前缀匹配，不需要处理CONTAINS的特殊语法，逻辑更简单，但性能远低于全文检索，不适合大数据量场景：

select * from Users where Name like concat(@Text, '%') and IsDeleted = 0

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