TSQL多字段WHERE子句CASE逻辑实现问题咨询

我来分享几个在SQL Server里实现这个需求的常用方案，你可以根据自己的场景和偏好来选择：

方案1：动态SQL拼接（灵活高效） #

这种方式会根据@searchType的值拼接对应的字段条件，生成最贴合需求的查询语句，性能表现通常不错。

CREATE PROCEDURE GetUsers
    @searchType VARCHAR(20),
    @userList VARCHAR(MAX) -- 假设传入逗号分隔的用户标识列表（如ID/账号）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    -- 基础查询框架
    SET @sql = N'SELECT * FROM 用户表 WHERE 1=1 ';

    -- 根据搜索类型拼接对应的过滤条件
    IF @searchType = 'lvl1Mgr'
        SET @sql += N'AND lvl1Mgr IN (SELECT value FROM STRING_SPLIT(@userList, '','')) ';
    ELSE IF @searchType = 'lvl2Mgr'
        SET @sql += N'AND lvl2Mgr IN (SELECT value FROM STRING_SPLIT(@userList, '','')) ';
    ELSE IF @searchType = 'lvl3Mgr'
        SET @sql += N'AND lvl3Mgr IN (SELECT value FROM STRING_SPLIT(@userList, '','')) ';

    -- 用参数化方式执行动态SQL，避免SQL注入风险
    EXEC sp_executesql @sql, N'@userList VARCHAR(MAX)', @userList;
END

注意事项：

• STRING_SPLIT是SQL Server 2016及以上版本支持的函数，如果你用的是更低版本，需要自己写一个自定义的字符串分割表值函数。
• 一定要用sp_executesql而非直接EXEC，通过参数化传递@userList可以彻底避免SQL注入问题。

方案2：静态SQL+多分支判断（易维护） #

如果不想用动态SQL，也可以用静态SQL的多分支条件来实现，代码更直观，维护起来也方便。

写法一：CASE表达式 #

CREATE PROCEDURE GetUsers
    @searchType VARCHAR(20),
    @userList VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM 用户表
    WHERE 
        CASE @searchType
            WHEN 'lvl1Mgr' THEN IIF(lvl1Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')), 1, 0)
            WHEN 'lvl2Mgr' THEN IIF(lvl2Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')), 1, 0)
            WHEN 'lvl3Mgr' THEN IIF(lvl3Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')), 1, 0)
            ELSE 0 -- 传入无效类型时返回空结果
        END = 1;
END

写法二：OR组合分支 #

这种写法可读性更强：

CREATE PROCEDURE GetUsers
    @searchType VARCHAR(20),
    @userList VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM 用户表
    WHERE 
        (@searchType = 'lvl1Mgr' AND lvl1Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')))
        OR (@searchType = 'lvl2Mgr' AND lvl2Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')))
        OR (@searchType = 'lvl3Mgr' AND lvl3Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ',')));
END

优点：静态SQL无需拼接，不会有动态SQL的注入顾虑；缺点：SQL Server可能会生成通用的执行计划，在数据量极大的场景下，性能可能不如动态SQL的针对性查询。

方案3：IF分支独立查询（最优性能） #

如果你的三个层级的查询后续可能有不同的扩展需求，或者追求极致性能，可以用IF分支拆分每个场景的查询逻辑：

CREATE PROCEDURE GetUsers
    @searchType VARCHAR(20),
    @userList VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    IF @searchType = 'lvl1Mgr'
    BEGIN
        SELECT * FROM 用户表 WHERE lvl1Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ','));
    END
    ELSE IF @searchType = 'lvl2Mgr'
    BEGIN
        SELECT * FROM 用户表 WHERE lvl2Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ','));
    END
    ELSE IF @searchType = 'lvl3Mgr'
    BEGIN
        SELECT * FROM 用户表 WHERE lvl3Mgr IN (SELECT value FROM STRING_SPLIT(@userList, ','));
    END
    ELSE
    BEGIN
        -- 处理无效的搜索类型，可选返回空结果或抛出错误
        RAISERROR('传入的搜索类型无效，请检查参数', 16, 1);
    END
END

优点：每个分支都是独立的查询，SQL Server会为每个场景生成最优的执行计划，性能最佳；缺点：代码有重复，后续如果要修改查询字段或基础逻辑，需要同步修改多个分支。

额外优化建议 #

• 建议给lvl1Mgr、lvl2Mgr、lvl3Mgr这三个字段分别建立非聚集索引，能大幅提升IN查询的效率。
• 如果@userList的格式不是逗号分隔，可根据实际情况调整分割逻辑。

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