SQL Server循环提取近12个月员工C1课程数据的存储过程错误排查

问题背景 #

• employee表结构：

+-----------+-------------+
| Field     | Type        |   
+-----------+-------------+    
| emp_name  | varchar(30) | 
| join_date | date        | 
| emp_id    | int(5)      | 
| c1_date   | date        | 
+-----------+-------------+

• 需求：从指定月份开始，按c1_date提取过去12个月每个月的员工详情。例如指定日期为2023年7月1日时，需分月展示对应月份c1_date的员工记录。
• 问题：编写的存储过程执行后未按单个月份筛选，而是返回所有符合条件的记录。

原存储过程（存在错误） #

CREATE PROCEDURE [dbo].[StudentReport] 
    @FromDate datetime =null
AS 

DECLARE @count INT;
SET @count = -1;

WHILE @count>= -12
BEGIN
   SELECT *
    FROM [Student]
    WHERE [Group Type] ='AIS-140' AND
    [c1_date] BETWEEN DATEADD(MONTH, @count, GETDATE()) AND GETDATE()
   SET @count = @count - 1;
END;

错误点分析 #

1. 表名不匹配：需求中操作的是employee表，但存储过程里查询的是Student表，查询对象错误。
2. 日期范围逻辑错误：原条件BETWEEN DATEADD(MONTH, @count, GETDATE()) AND GETDATE()会返回从@count个月前到当前的所有数据，例如@count=-2时，范围是过去2个月到现在，会包含过去1个月和过去2个月的所有数据，而非仅过去第2个月的记录。
3. 未使用传入参数：存储过程定义了@FromDate参数，但实际查询用了GETDATE()，不符合“从指定月份开始”的需求。
4. 缺少月份边界处理：没有限定每个查询仅对应单个月份的起始和结束日期，导致范围越界。

修正后的存储过程 #

CREATE PROCEDURE [dbo].[EmployeeC1Report] 
    @FromDate datetime = NULL
AS 
-- 默认使用当前日期的月初作为起始点
SET @FromDate = ISNULL(@FromDate, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()))

DECLARE @count INT;
SET @count = -1;

WHILE @count >= -12
BEGIN
    -- 计算当前循环对应的月份的第一天和最后一天
    DECLARE @MonthStart DATE = DATEADD(MONTH, @count, @FromDate)
    DECLARE @MonthEnd DATE = EOMONTH(@MonthStart)

    SELECT 
        emp_id, emp_name, join_date, c1_date,
        -- 增加月份标识，方便区分每个月的结果
        DATENAME(MONTH, @MonthStart) + ' ' + DATENAME(YEAR, @MonthStart) AS [所属月份]
    FROM [employee]
    WHERE 
        [Group Type] = 'AIS-140' 
        AND c1_date BETWEEN @MonthStart AND @MonthEnd

    SET @count = @count - 1;
END;

修正说明 #

• 修正表名为employee，匹配需求中的表结构。
• 使用@FromDate参数，若未传入则默认使用当前月份的第一天。
• 用EOMONTH函数获取每个月份的最后一天，确保每个查询仅返回对应单个月份的c1_date记录。
• 增加所属月份字段，方便区分不同循环返回的月度数据。

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