优化带日期条件的SQL存储过程：支持空日期参数查询需求

现有表结构与数据 #

创建表SQL #

CREATE TABLE [dbo].[RiskTable]
(
    [ReqID] [int] NOT NULL,
    [ModuleName] [nvarchar](50) NULL,
    [CreatedDate] [datetime2](7) NULL,
    [AssociatedRisk] [nvarchar](50) NULL
)

表中数据 #

现有存储过程逻辑与代码 #

已实现逻辑 #

• 场景1：传入model='saa'、start_date='2023-01-01'、end_date='2023-02-10'，统计该时间段内Saa模型各风险等级的总数。
• 场景2：传入model=NULL、start_date='2023-04-01'、end_date='2023-05-11'，统计该时间段内所有模型各风险等级的总数，示例输出如下：

原存储过程代码 #

CREATE PROCEDURE Month_base_global
            @model varchar(20),   
            @start_date date,
            @end_date date
AS
BEGIN
IF @model = 'ALL'
    BEGIN
      SELECT 'ALL' as [Module], ac.[Month], ac.AssociatedRisk as Risk, count(ri.ModuleName) as   'Total risks'
    FROM (
      SELECT DISTINCT convert(varchar(7), r.CreatedDate, 126) as [month], a.AssociatedRisk,  m.ModuleName
      FROM RiskTable r
      FULL OUTER JOIN (
        SELECT DISTINCT AssociatedRisk
        FROM RiskTable
      ) as a on a.AssociatedRisk is not null
      FULL OUTER JOIN (
         SELECT DISTINCT ModuleName
         FROM RiskTable
      ) as m on m.ModuleName is not null
      WHERE (r.CreatedDate) between (@start_date) and (@end_date) 
    ) as ac
    LEFT JOIN RiskTable ri on ac.[month] = convert(varchar(7), ri.CreatedDate, 126)
                           and ac.AssociatedRisk = ri.AssociatedRisk
                           and ac.ModuleName = ri.ModuleName
    GROUP BY ac.[month], ac.AssociatedRisk
    ORDER BY ac.[month], ac.AssociatedRisk desc
END
ELSE
    BEGIN
    SELECT @model as [Module], ac.[Month], ac.AssociatedRisk as Risk, count(ri.ModuleName) as 'Total risks'
    FROM (
      SELECT DISTINCT convert(varchar(7), r.CreatedDate, 126) as [month], a.AssociatedRisk, m.ModuleName
      FROM RiskTable r
      FULL OUTER JOIN (
        SELECT DISTINCT AssociatedRisk
        FROM RiskTable
      ) as a on a.AssociatedRisk is not null
      FULL OUTER JOIN (
         SELECT DISTINCT ModuleName
         FROM RiskTable
      ) as m on m.ModuleName = @model
        WHERE r.CreatedDate between DATEADD(mm, DATEDIFF(mm, 0, @start_date), 0) and eomonth(@end_date)
    ) as ac
    LEFT JOIN RiskTable ri on ac.[month] = convert(varchar(7), ri.CreatedDate, 126)
                           and ac.AssociatedRisk = ri.AssociatedRisk
                           and ac.ModuleName = ri.ModuleName
    GROUP BY ac.ModuleName, ac.[month], ac.AssociatedRisk
    ORDER BY ac.[month], ac.AssociatedRisk desc
END
END
Exec Month_base_global 'ALL' , '2022-02-01', '2023-01-28'

新增需求 #

需要为存储过程新增以下3种空参数场景的处理逻辑：

• 场景3：传入model为ALL或特定模型、start_date=NULL、end_date='2023-01-28'，查询该模型（或所有模型）从表中最早记录到指定结束日期的所有风险统计。
• 场景4：传入model为ALL或特定模型、start_date='2022-12-03'、end_date=NULL，查询该模型（或所有模型）从指定开始日期到当前日期的所有风险统计。
• 场景5：传入model为ALL或特定模型、start_date=NULL、end_date=NULL，查询该模型（或所有模型）从表中最早记录到当前日期的所有风险统计。

修改后的存储过程实现 #

CREATE PROCEDURE Month_base_global
    @model varchar(20),   
    @start_date date,
    @end_date date
AS
BEGIN
    -- 预处理参数：替换空值为对应默认值
    DECLARE @actual_start_date date, @actual_end_date date;
    
    -- 起始日期为空则取表中最早的记录日期
    SELECT @actual_start_date = ISNULL(@start_date, MIN(CreatedDate)) 
    FROM RiskTable;
    
    -- 结束日期为空则取当前日期
    SET @actual_end_date = ISNULL(@end_date, CAST(GETDATE() AS date));

    -- model为NULL时视为查询所有模型
    SET @model = ISNULL(@model, 'ALL');

    IF @model = 'ALL'
    BEGIN
        SELECT 
            'ALL' as [Module], 
            -- 格式化月份为"Apr or 04"样式
            CONCAT(
                DATENAME(month, DATEFROMPARTS(YEAR(CAST(ac.[month] + '-01' AS date)), MONTH(CAST(ac.[month] + '-01' AS date)), 1)), 
                ' or ', 
                RIGHT('0' + CAST(MONTH(CAST(ac.[month] + '-01' AS date)) AS varchar(2)), 2)
            ) AS [Month],
            ac.AssociatedRisk as Risk, 
            COUNT(ri.ReqID) as [Total no. of risk]
        FROM (
            -- 生成时间段内所有月份与所有风险等级的组合
            SELECT DISTINCT 
                CONVERT(varchar(7), r.CreatedDate, 126) as [month], 
                a.AssociatedRisk
            FROM RiskTable r
            CROSS JOIN (SELECT DISTINCT AssociatedRisk FROM RiskTable) a
            WHERE r.CreatedDate BETWEEN @actual_start_date AND @actual_end_date
        ) ac
        LEFT JOIN RiskTable ri 
            ON CONVERT(varchar(7), ri.CreatedDate, 126) = ac.[month]
            AND ri.AssociatedRisk = ac.AssociatedRisk
        GROUP BY ac.[month], ac.AssociatedRisk
        ORDER BY ac.[month], 
            -- 按风险优先级排序：Critical > High > Medium > Low
            CASE ac.AssociatedRisk 
                WHEN 'Critical' THEN 1 
                WHEN 'High' THEN 2 
                WHEN 'Medium' THEN 3 
                WHEN 'Low' THEN 4 
                ELSE 5 
            END
    END
    ELSE
    BEGIN
        SELECT 
            @model as [Module], 
            CONCAT(
                DATENAME(month, DATEFROMPARTS(YEAR(CAST(ac.[month] + '-01' AS date)), MONTH(CAST(ac.[month] + '-01' AS date)), 1)), 
                ' or ', 
                RIGHT('0' + CAST(MONTH(CAST(ac.[month] + '-01' AS date)) AS varchar(2)), 2)
            ) AS [Month],
            ac.AssociatedRisk as Risk, 
            COUNT(ri.ReqID) as [Total no. of risk]
        FROM (
            SELECT DISTINCT 
                CONVERT(varchar(7), r.CreatedDate, 126) as [month], 
                a.AssociatedRisk
            FROM RiskTable r
            CROSS JOIN (SELECT DISTINCT AssociatedRisk FROM RiskTable) a
            WHERE r.CreatedDate BETWEEN @actual_start_date AND @actual_end_date
              AND r.ModuleName = @model
        ) ac
        LEFT JOIN RiskTable ri 
            ON CONVERT(varchar(7), ri.CreatedDate, 126) = ac.[month]
            AND ri.AssociatedRisk = ac.AssociatedRisk
            AND ri.ModuleName = @model
        GROUP BY ac.[month], ac.AssociatedRisk
        ORDER BY ac.[month], 
            CASE ac.AssociatedRisk 
                WHEN 'Critical' THEN 1 
                WHEN 'High' THEN 2 
                WHEN 'Medium' THEN 3 
                WHEN 'Low' THEN 4 
                ELSE 5 
            END
    END
END

关键修改说明 #

1. 参数统一预处理：自动处理空参数，无需调用方额外判断，简化使用逻辑。
2. 月份格式对齐：将原YYYY-MM格式转换为需求指定的Apr or 04样式，匹配示例输出。
3. 风险排序优化：按业务优先级排序风险等级，比原字符串倒序更符合实际需求。
4. 统计准确性提升：使用非空字段ReqID进行计数，避免因ModuleName为NULL导致的统计偏差。
5. 逻辑简化：用CROSS JOIN替代原复杂的FULL OUTER JOIN，更简洁生成所有月份与风险等级的组合，确保每个风险等级在每个月份都有记录（即使总数为0）。

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