创建企业非工作时段临时表的存储过程问题排查咨询

原代码核心问题 #

1. 参数逻辑优先级错误：用户传入的日期参数永远不会生效，代码始终使用默认近3年的时间范围
2. 周五时段计算错误：原逻辑生成了结束时间早于开始时间的非法区间，不符合周五18:00至周六00:00的规则
3. 未显式指定周计算规则：不同SQL Server实例的默认DATEFIRST配置会导致星期判断结果错乱

修正后完整代码 #

ALTER PROCEDURE [dbo].[temptableforoffhours]
    @BDate  varchar(50),
    @EDate   varchar(50),
    @Provider   varchar(1000)=''
AS
BEGIN
SET NOCOUNT ON
SET DATEFIRST 7 -- 显式指定周日为每周第一天，保证星期计算逻辑统一
    
DECLARE 
    @BeginDate  datetime,
    @EndDate    DATETIME

-- 优先使用用户传入的日期参数
IF ISNULL(@BDate, '') <> '' AND ISNULL(@EDate, '') <> ''
BEGIN
    SET @BeginDate = CONVERT(datetime, @BDate, 121)
    SET @EndDate = CONVERT(datetime, CAST(DATEADD(DAY, 1, @EDate) AS DATE), 121)
END
-- 未传参数时使用默认近3年范围
ELSE
BEGIN 
    SET @BeginDate = DATEADD(YEAR, -3, GETDATE())
    SET @EndDate = GETDATE()
END

/********************************Creation of #tmptimeFrameAudit table with FrameID and Start_day and end_Day********************************/
DECLARE @CountTimeFrames INT = DATEDIFF(DAY, @BeginDate, @EndDate)
DECLARE @Counter INT = 1

CREATE TABLE #tmptimeFrameAudit (
    Frameid INT,
    Start_Day DATETIME, 
    End_Day DATETIME, 
    doW VARCHAR(10)
)

WHILE (@Counter <= @CountTimeFrames)
BEGIN
    IF @Counter > 1
    BEGIN
        SET @BeginDate = DATEADD(DAY, 1, @BeginDate)
    END

    IF DATEPART(WEEKDAY, @BeginDate) = 7 -- 周六
    BEGIN
        INSERT INTO #tmptimeFrameAudit 
        VALUES (@Counter, @BeginDate, DATEADD(HOUR, 24, @BeginDate), DATENAME(WEEKDAY, @BeginDate))
    END
    ELSE IF DATEPART(WEEKDAY, @BeginDate) = 6 -- 周五
    BEGIN
        INSERT INTO #tmptimeFrameAudit 
        VALUES (@Counter, DATEADD(HOUR, 18, @BeginDate), DATEADD(DAY, 1, @BeginDate), DATENAME(WEEKDAY, @BeginDate))
    END
    ELSE IF DATEPART(WEEKDAY, @BeginDate) = 1 -- 周日
    BEGIN
        INSERT INTO #tmptimeFrameAudit 
        VALUES (@Counter, @BeginDate, DATEADD(HOUR, 30, @BeginDate), DATENAME(WEEKDAY, @BeginDate))
    END
    ELSE -- 周一至周四（工作日）
    BEGIN
        INSERT INTO #tmptimeFrameAudit 
        VALUES (@Counter, DATEADD(HOUR, 18, @BeginDate), DATEADD(HOUR, 30, @BeginDate), DATENAME(WEEKDAY, @BeginDate))
    END     

    SET @Counter = @Counter + 1
END

-- 输出格式与需求示例对齐
SELECT 
    LEFT(doW, 3) AS 起始星期,
    CONVERT(VARCHAR(23), Start_Day, 101) + ' ' + CONVERT(VARCHAR(12), Start_Day, 114) AS 开始日期,
    LEFT(DATENAME(WEEKDAY, End_Day), 3) AS 结束星期,
    CONVERT(VARCHAR(23), End_Day, 101) + ' ' + CONVERT(VARCHAR(12), End_Day, 114) AS 结束日期
FROM #tmptimeFrameAudit

DROP TABLE #tmptimeFrameAudit
END

验证方法 #

传入测试参数执行即可得到示例中的输出：

EXEC [dbo].[temptableforoffhours] @BDate='2021-08-01', @EDate='2021-08-10'

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