报表场景下可选参数驱动SQL查询的最佳实践咨询

需要处理约100万条客户订单数据生成报表，涉及大量计算、交叉引用，因此采用集中式数据检索存储过程获取原始数据并完成所有计算，避免每个报表重复编写1000行T-SQL，以此降低维护成本并保证SSRS报表的数据一致性。

为适配报表筛选条件，存储过程中使用了如下参数化WHERE子句：

WHERE
    (
        (WasteOrderDetail.Active = 1) AND
        (WasteOrderDetail.WasteOrderStatusId IN(2,7,8,9)) AND
        (
            (
                (CAST(WasteOrderDetail.ActionDate AS DATE) >= @LocalStartDate) AND  
                (@LocalStartDate IS NOT NULL) 
            ) OR
            (
                (@LocalStartDate IS NULL) 
            )
        ) AND
        (
            (
                (CAST(WasteOrderDetail.ActionDate AS DATE) <= @LocalEndDate) AND
                (@LocalEndDate IS NOT NULL) 
            ) OR
            (
                (@LocalEndDate IS NULL) 
            )
        )  AND
        (
            (
                (
                    (ClientHierarchy2.ParentClientId = @LocalClientId) AND
                    (@LocalClientId IS NOT NULL) 
                )
            ) OR
            (
                (
                    (ClientHierarchy1.ParentClientId = @LocalClientId) AND
                    (@LocalClientId IS NOT NULL) 
                )
            ) OR
            (
                (
                    (Site.ClientId = @LocalClientId) AND
                    (@LocalClientId IS NOT NULL) 
                )
            ) OR
            (
                (@LocalClientId IS NULL) 
            )
        ) AND
        (
            (
                (WasteOrderHeader.SiteId = @LocalSiteId) AND
                (@LocalSiteId IS NOT NULL) 
            ) OR
            (
                (@LocalSiteId IS NULL) 
            )
        ) AND 
        (
            (
                (WasteOrderHeader.WasteOrderHeaderId = @LocalWasteOrderHeaderId) AND
                (@LocalWasteOrderHeaderId IS NOT NULL) 
            ) OR
            (
                (@LocalWasteOrderHeaderId IS NULL) 
            )
        ) AND 
        (
            (
                (WasteOrderDetail.WasteOrderDetailId = @LocalWasteOrderDetailId) AND
                (@LocalWasteOrderDetailId IS NOT NULL) 
            ) OR
            (
                (@LocalWasteOrderDetailId IS NULL) 
            )
        ) AND 
        (
            (
                (Facility.ContractorId = @LocalContractorId) AND
                (@LocalContractorId IS NOT NULL) 
            ) OR
            (
                (@LocalContractorId IS NULL) 
            )
        ) AND 
        (
            (
                (WasteOrderDetail.FacilityId = @LocalFacilityId) AND
                (@LocalFacilityId IS NOT NULL) 
            ) OR
            (
                (@LocalFacilityId IS NULL) 
            )
        ) AND 
        (
            (
                (DATEPART(YEAR,WasteOrderDetail.ActionDate) = @LocalYear) AND
                (@LocalYear IS NOT NULL)
            ) OR
            (
                (@LocalYear IS NULL)
            )
        ) AND 
        (
            (
                (DATEPART(MONTH,WasteOrderDetail.ActionDate) = @LocalMonth) AND
                (@LocalMonth IS NOT NULL) 
            ) OR
            (
                (@LocalMonth IS NULL)
            )
        ) AND
        (
            (@LocalClientId IS NOT NULL) OR
            (@LocalSiteId IS NOT NULL) OR
            (@LocalWasteOrderHeaderId IS NOT NULL) OR
            (@LocalWasteOrderDetailId IS NOT NULL)  OR
            (@LocalContractorId IS NOT NULL) OR
            (@LocalFacilityId IS NOT NULL) OR
            (@LocalStartDate IS NOT NULL) OR
            (@LocalEndDate IS NOT NULL) OR
            (@LocalYear IS NOT NULL) OR
            (@LocalMonth IS NOT NULL) 
        )
    )

已将输入参数转换为本地参数避免参数嗅探，但当前方案存在执行计划问题（曾尝试带CTE的视图实现集中检索，但参数化筛选导致性能下降），已知此类条件参数块的弊端，寻求可维护且集中式数据检索的通用解决方案。

1. 动态SQL构建精准查询 #

动态SQL会根据传入的参数值，只生成实际需要的筛选条件，避免OR逻辑导致的执行计划低效。同时通过sp_executesql实现参数化，防止SQL注入，且能复用执行计划。

示例框架：

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Params NVARCHAR(MAX) = '@LocalStartDate DATE, @LocalEndDate DATE, @LocalClientId INT, @LocalSiteId INT, @LocalWasteOrderHeaderId INT, @LocalWasteOrderDetailId INT, @LocalContractorId INT, @LocalFacilityId INT, @LocalYear INT, @LocalMonth INT'

SET @SQL = N'
SELECT -- 替换为你的计算字段与列表
    WasteOrderDetail.*,
    ClientHierarchy2.ParentClientId AS Level2ParentId,
    ClientHierarchy1.ParentClientId AS Level1ParentId,
    Site.ClientId AS SiteClientId
FROM WasteOrderDetail
JOIN ClientHierarchy2 ON WasteOrderDetail.ClientId = ClientHierarchy2.ClientId
JOIN ClientHierarchy1 ON WasteOrderDetail.ClientId = ClientHierarchy1.ClientId
JOIN Site ON WasteOrderDetail.SiteId = Site.SiteId
JOIN WasteOrderHeader ON WasteOrderDetail.WasteOrderHeaderId = WasteOrderHeader.WasteOrderHeaderId
JOIN Facility ON WasteOrderDetail.FacilityId = Facility.FacilityId
WHERE WasteOrderDetail.Active = 1
  AND WasteOrderDetail.WasteOrderStatusId IN(2,7,8,9)
'

-- 拼接日期筛选
IF @LocalStartDate IS NOT NULL
    SET @SQL += N' AND CAST(WasteOrderDetail.ActionDate AS DATE) >= @LocalStartDate'
IF @LocalEndDate IS NOT NULL
    SET @SQL += N' AND CAST(WasteOrderDetail.ActionDate AS DATE) <= @LocalEndDate'

-- 拼接客户ID筛选
IF @LocalClientId IS NOT NULL
    SET @SQL += N' AND (ClientHierarchy2.ParentClientId = @LocalClientId OR ClientHierarchy1.ParentClientId = @LocalClientId OR Site.ClientId = @LocalClientId)'

-- 拼接其他参数筛选
IF @LocalSiteId IS NOT NULL
    SET @SQL += N' AND WasteOrderHeader.SiteId = @LocalSiteId'
IF @LocalWasteOrderHeaderId IS NOT NULL
    SET @SQL += N' AND WasteOrderHeader.WasteOrderHeaderId = @LocalWasteOrderHeaderId'
-- 剩余参数筛选逻辑同理...

-- 确保至少有一个筛选条件生效
SET @SQL += N' AND ('
SET @SQL += CASE WHEN @LocalClientId IS NOT NULL THEN N'1=1 OR ' ELSE N'' END
SET @SQL += CASE WHEN @LocalSiteId IS NOT NULL THEN N'1=1 OR ' ELSE N'' END
SET @SQL += CASE WHEN @LocalStartDate IS NOT NULL THEN N'1=1 OR ' ELSE N'' END
-- 剩余参数的非空检查
SET @SQL += N'1=0)'

EXEC sp_executesql @SQL, @Params, 
    @LocalStartDate = @LocalStartDate, 
    @LocalEndDate = @LocalEndDate,
    @LocalClientId = @LocalClientId,
    @LocalSiteId = @LocalSiteId,
    @LocalWasteOrderHeaderId = @LocalWasteOrderHeaderId,
    @LocalWasteOrderDetailId = @LocalWasteOrderDetailId,
    @LocalContractorId = @LocalContractorId,
    @LocalFacilityId = @LocalFacilityId,
    @LocalYear = @LocalYear,
    @LocalMonth = @LocalMonth

优势：生成的查询语句简洁，SQL Server能针对实际筛选条件生成最优执行计划；集中维护筛选逻辑，报表只需调用存储过程。

2. 使用OPTION(RECOMPILE)优化执行计划 #

在存储过程的查询末尾添加OPTION(RECOMPILE)，强制SQL Server每次执行时根据当前参数值重新生成执行计划，避免通用执行计划适配所有参数场景的低效问题。

示例：

SELECT -- 你的计算字段与列表
FROM ... -- 关联表逻辑
WHERE -- 原有的参数化WHERE子句
OPTION(RECOMPILE)

注意：此方案会增加每次执行的编译开销，适合报表查询频率不高、参数组合差异大的场景。结合本地参数（已实现），能进一步缓解参数嗅探问题。

3. 拆分存储过程：基础数据集+参数化筛选 #

将无参数的基础计算逻辑（如交叉引用、固定计算）封装为一个视图或表值函数，然后创建一个参数化的存储过程，在其中调用基础数据集并添加筛选条件。

示例：

-- 第一步：创建基础视图，包含所有固定计算与关联
CREATE VIEW vw_OrderBaseData AS
SELECT 
    WasteOrderDetail.*,
    ClientHierarchy2.ParentClientId AS Level2ParentId,
    ClientHierarchy1.ParentClientId AS Level1ParentId,
    Site.ClientId AS SiteClientId,
    -- 其他固定计算字段
FROM WasteOrderDetail
JOIN ClientHierarchy2 ON WasteOrderDetail.ClientId = ClientHierarchy2.ClientId
JOIN ClientHierarchy1 ON WasteOrderDetail.ClientId = ClientHierarchy1.ClientId
JOIN Site ON WasteOrderDetail.SiteId = Site.SiteId
WHERE WasteOrderDetail.Active = 1
  AND WasteOrderDetail.WasteOrderStatusId IN(2,7,8,9)

-- 第二步：参数化存储过程
CREATE PROCEDURE sp_GetFilteredOrderData
    @LocalStartDate DATE = NULL,
    @LocalEndDate DATE = NULL,
    @LocalClientId INT = NULL,
    @LocalSiteId INT = NULL,
    -- 其他参数
AS
BEGIN
    SELECT *
    FROM vw_OrderBaseData
    JOIN WasteOrderHeader ON WasteOrderHeaderId = WasteOrderHeader.WasteOrderHeaderId
    JOIN Facility ON FacilityId = Facility.FacilityId
    WHERE 
        (@LocalStartDate IS NULL OR CAST(ActionDate AS DATE) >= @LocalStartDate)
        AND (@LocalEndDate IS NULL OR CAST(ActionDate AS DATE) <= @LocalEndDate)
        AND (@LocalClientId IS NULL OR (Level2ParentId = @LocalClientId OR Level1ParentId = @LocalClientId OR SiteClientId = @LocalClientId))
        AND (@LocalSiteId IS NULL OR WasteOrderHeader.SiteId = @LocalSiteId)
        -- 其他筛选条件
        AND (
            @LocalClientId IS NOT NULL OR @LocalSiteId IS NOT NULL OR @LocalStartDate IS NOT NULL OR @LocalEndDate IS NOT NULL
        )
    OPTION(RECOMPILE) -- 可选，根据性能情况添加
END

优势：基础逻辑集中维护，筛选逻辑单独管理；视图能预先优化关联与计算，存储过程专注于参数筛选。

4. 使用表值参数传递筛选条件 #

将多个筛选条件封装为一个表值参数（TVP），存储过程中基于TVP进行筛选。此方案适合筛选条件复杂或需要批量传递筛选值的场景。

示例：

-- 定义表值参数类型
CREATE TYPE OrderFilterType AS TABLE (
    FilterType NVARCHAR(50), -- 如StartDate, EndDate, ClientId等
    FilterValue SQL_VARIANT
)

-- 存储过程
CREATE PROCEDURE sp_GetFilteredOrders
    @Filters OrderFilterType READONLY
AS
BEGIN
    DECLARE @LocalStartDate DATE = (SELECT FilterValue FROM @Filters WHERE FilterType = 'StartDate')
    DECLARE @LocalEndDate DATE = (SELECT FilterValue FROM @Filters WHERE FilterType = 'EndDate')
    DECLARE @LocalClientId INT = (SELECT FilterValue FROM @Filters WHERE FilterType = 'ClientId')
    -- 其他参数赋值

    SELECT -- 字段与计算
    FROM ... -- 关联表逻辑
    WHERE
        WasteOrderDetail.Active = 1
        AND WasteOrderDetail.WasteOrderStatusId IN(2,7,8,9)
        AND (@LocalStartDate IS NULL OR CAST(ActionDate AS DATE) >= @LocalStartDate)
        AND (@LocalEndDate IS NULL OR CAST(ActionDate AS DATE) <= @LocalEndDate)
        AND (@LocalClientId IS NULL OR (ClientHierarchy2.ParentClientId = @LocalClientId OR ClientHierarchy1.ParentClientId = @LocalClientId OR Site.ClientId = @LocalClientId))
END

优势：筛选条件结构灵活，便于扩展新的筛选维度，集中管理筛选逻辑。

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