SQL Server存储过程多可选参数过滤的最高效优化方案是什么

原方案存在的问题 #

• 存在严重SQL注入风险：直接将用户输入的参数拼入SQL语句，恶意构造的参数可以执行任意SQL操作
• 执行计划复用率低：不同参数组合生成的SQL语句结构不同，数据库无法重用缓存的执行计划，导致每次执行都需要重新编译，影响效率
• 语法存在隐患：原拼接语句的引号写法存在错误，实际运行会出现语法问题，字符串转义处理容易出错

推荐优化方案 #

方案1：参数化动态SQL（推荐，兼顾灵活性和性能） #

该方案保留动态SQL的灵活性，同时通过参数化传参避免注入风险，还能大幅提升执行计划复用率，示例代码如下：

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @vQuery nvarchar(max)
    SET @vQuery = N'SELECT * FROM [TABLE] WHERE 1=1 '

    IF (@pItem IS NOT NULL) 
        SET @vQuery += N'AND ITEM LIKE @pItem '
    IF (@pCity IS NOT NULL) 
        SET @vQuery += N'AND CITY LIKE @pCity '
    IF (@pSerialN IS NOT NULL) 
        SET @vQuery += N'AND SerialN LIKE @pSerialN '
    IF (@pPromise IS NOT NULL) 
        SET @vQuery += N'AND Promise = @pPromise '
    IF (@pSalesOrder IS NOT NULL) 
        SET @vQuery += N'AND SalesOrder LIKE @pSalesOrder '
    IF (@pLineNumber IS NOT NULL) 
        SET @vQuery += N'AND LineNumber = @pLineNumber '

    -- 参数化执行，避免注入，提升执行计划复用率
    EXEC sp_executesql @vQuery,
        N'@pItem nvarchar(max),
          @pCity nvarchar(max),
          @pSerialN nvarchar(max),
          @pPromise datetime,
          @pSalesOrder nvarchar(max),
          @pLineNumber int',
        @pItem = @pItem,
        @pCity = @pCity,
        @pSerialN = @pSerialN,
        @pPromise = @pPromise,
        @pSalesOrder = @pSalesOrder,
        @pLineNumber = @pLineNumber
    -- 若需要更高的执行计划匹配度，可根据业务场景选择添加 OPTION (RECOMPILE)
END

方案优势：

• 完全避免SQL注入风险，所有参数都通过sp_executesql规范传递
• 相同参数组合的查询可以复用缓存的执行计划，减少编译开销
• 不需要处理字符串转义，不会出现引号类语法错误

方案2：静态SQL条件写法（适合参数数量较少的场景） #

不需要拼接SQL，直接在WHERE条件中判断参数是否为NULL，代码更简洁：

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * 
    FROM [TABLE] 
    WHERE 
        (@pItem IS NULL OR ITEM LIKE @pItem)
        AND (@pCity IS NULL OR CITY LIKE @pCity)
        AND (@pSerialN IS NULL OR SerialN LIKE @pSerialN)
        AND (@pPromise IS NULL OR Promise = @pPromise)
        AND (@pSalesOrder IS NULL OR SalesOrder LIKE @pSalesOrder)
        AND (@pLineNumber IS NULL OR LineNumber = @pLineNumber)
    -- 可选添加重编译选项，让SQL生成适配当前参数的最优执行计划
    -- OPTION (RECOMPILE)
END

方案优势：

• 代码结构简单，没有拼接逻辑，易于维护
• 不需要处理动态SQL的各类语法问题

注意：如果参数数量较多、或查询涉及的表数据量很大，建议添加OPTION (RECOMPILE)让数据库每次根据传入的参数生成最优执行计划，避免参数嗅探问题。

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