如何在SQL存储过程中添加动态WHERE子句实现条件过滤

你原来用CASE拼接WHERE子句的写法是错误的——CASE是返回单个值的表达式，无法用来构造SQL语句的逻辑片段。下面提供两种可行的解决方案，兼顾安全性和灵活性：

方案一：使用动态SQL（推荐，安全且灵活） #

通过参数化动态SQL实现逻辑，彻底避免SQL注入风险，同时支持灵活扩展过滤条件：

ALTER PROCEDURE [dbo].[sp_getQuickReportsData] 
    @dateClosedFrom DATE,
    @dateClosedTo DATE,
    @dateNotifiedFrom DATE,
    @dateNotifiedTo DATE,
    @filter NVARCHAR(50) = NULL,
    @filterValues NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    DECLARE @params NVARCHAR(MAX);

    -- 基础查询语句
    SET @sql = N'
        SELECT
            b.FirstName + '' '' + b.LastName AS ClaimHandler
            , c.[Claim Ref] AS ClaimRef
            , c.Profession
            , c.Insured
            , c.ClientDivision
            , c.ClientSiteOrAccidentLossLocation AS LossLocation
        FROM 
            ClaimData c 
            LEFT JOIN CSL_LIB.dbo.Security_Users b ON b.ID = c.ClaimHandlerID
        WHERE 
            DateClosed >= @dateClosedFrom AND DateClosed <= @dateClosedTo
            AND DateNotified >= @dateNotifiedFrom AND DateNotified <= @dateNotifiedTo';

    -- 根据filter参数添加额外过滤条件
    IF @filter IS NOT NULL AND @filterValues IS NOT NULL
    BEGIN
        SET @sql = @sql + N'
            AND ' + CASE @filter
                WHEN 'claimhandler' THEN N'b.FirstName + '' '' + b.LastName IN (SELECT value FROM STRING_SPLIT(@filterValues, '',''))'
                WHEN 'reference' THEN N'c.[Claim Ref] IN (SELECT value FROM STRING_SPLIT(@filterValues, '',''))'
                ELSE N'1=1' -- 非法filter值时添加恒真条件，不影响原有查询
            END;
    END

    -- 定义参数映射，确保所有输入参数都被参数化
    SET @params = N'
        @dateClosedFrom DATE,
        @dateClosedTo DATE,
        @dateNotifiedFrom DATE,
        @dateNotifiedTo DATE,
        @filterValues NVARCHAR(500)';

    -- 执行动态SQL
    EXEC sp_executesql @sql, @params,
        @dateClosedFrom = @dateClosedFrom,
        @dateClosedTo = @dateClosedTo,
        @dateNotifiedFrom = @dateNotifiedFrom,
        @dateNotifiedTo = @dateNotifiedTo,
        @filterValues = @filterValues;
END
GO

方案说明 #

• 使用sp_executesql执行动态SQL，所有参数均做参数化处理，彻底规避SQL注入风险。
• STRING_SPLIT用于将逗号分隔的@filterValues拆分成表，适配IN子句的查询要求（SQL Server 2016及以上版本支持，低版本可替换为自定义字符串拆分函数）。
• 通过CASE语句根据@filter的值匹配对应的过滤字段，非法filter值不会影响原有查询逻辑。

方案二：不使用动态SQL（适合简单场景） #

通过条件判断直接在WHERE子句中嵌入逻辑，无需拼接SQL语句：

ALTER PROCEDURE [dbo].[sp_getQuickReportsData] 
    @dateClosedFrom DATE,
    @dateClosedTo DATE,
    @dateNotifiedFrom DATE,
    @dateNotifiedTo DATE,
    @filter NVARCHAR(50) = NULL,
    @filterValues NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        b.FirstName + ' ' + b.LastName AS ClaimHandler
        , c.[Claim Ref] AS ClaimRef
        , c.Profession
        , c.Insured
        , c.ClientDivision
        , c.ClientSiteOrAccidentLossLocation AS LossLocation
    FROM 
        ClaimData c 
        LEFT JOIN CSL_LIB.dbo.Security_Users b ON b.ID = c.ClaimHandlerID
    WHERE 
        DateClosed >= @dateClosedFrom AND DateClosed <= @dateClosedTo
        AND DateNotified >= @dateNotifiedFrom AND DateNotified <= @dateNotifiedTo
        -- 处理claimhandler过滤：filter不匹配时条件自动失效
        AND (
            @filter <> 'claimhandler' 
            OR b.FirstName + ' ' + b.LastName IN (SELECT value FROM STRING_SPLIT(@filterValues, ','))
        )
        -- 处理reference过滤：filter不匹配时条件自动失效
        AND (
            @filter <> 'reference' 
            OR c.[Claim Ref] IN (SELECT value FROM STRING_SPLIT(@filterValues, ','))
        )
        -- 限制仅合法filter值生效
        AND (
            @filter IS NULL 
            OR @filter IN ('claimhandler', 'reference')
        );
END
GO

方案说明 #

• 每个过滤条件通过OR与参数判断结合，当@filter不是对应值时，该条件自动变为真，不影响原有查询。
• 最后一行的条件用于过滤非法的@filter值，避免无效参数干扰查询逻辑。
• 同样依赖STRING_SPLIT，低版本SQL Server需替换为自定义拆分函数。

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