代码之家 › 专栏 › 技术社区 › Kiel

当循环错误时设置DateFirst游标-设置选项已更改

cursor while-loop date sql-server

0

Kiel · 技术社区 · 7 年前

我的光标循环有很奇怪的行为,这是我得到的;

DECLARE @StartDate AS DATE
DECLARE @ID INT
DECLARE CursorTest CURSOR FOR  
SELECT ID FROM tblSomething

OPEN Schedule
FETCH NEXT FROM CursorTest INTO @ID

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @StartDate = StartDate FROM tblAnother WHERE ID = @ID
        SELECT @StartDate --12/06/2018
        -- NOW WE MOD IT
        SET DATEFIRST 6 -- WE START ON SATURDAY 1
        SET @StartDate = DATEPART(dw,@StartDate)
        SELECT @StartDate -- ANSWER IS 4
        FETCH NEXT FROM CursorTest INTO @ID
    END

CLOSE CursorTest
DEALLOCATE CursorTest

现在,如果我运行这个,我将到达光标上的第二行,它将崩溃并状态;

Could not complete cursor operation because the set options have changed since the cursor was declared.

现在如果我评论一下;

--SET DATEFIRST 6

错误消失了,所以我认为set datefirst正在修改导致错误的数据库。

在这附近有没有使用类似的东西;

SET @StartDate = DATEPART(dw,@StartDate,DATEFIRST 6)

一些类似的东西。

2 回复 | 直到 7 年前

1

Lukasz Szozda 7 年前

你可以搬家 SET :

DECLARE @StartDate AS DATE
DECLARE @ID INT
DECLARE CursorTest CURSOR FOR  
SELECT ID FROM tblSomething

SET DATEFIRST 6 -- WE START ON SATURDAY 1

OPEN Schedule
FETCH NEXT FROM CursorTest INTO @ID

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @StartDate = StartDate FROM tblAnother WHERE ID = @ID
        SELECT @StartDate --12/06/2018
        -- NOW WE MOD IT

        SET @StartDate = DATEPART(dw,@StartDate)
        SELECT @StartDate -- ANSWER IS 4
        FETCH NEXT FROM CursorTest INTO @ID
    END

CLOSE CursorTest
DEALLOCATE CursorTest

2

0

Kiel 7 年前

我修理了它;

SET DATEFIRST 6 -- outside CURSOR LOOP;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @StartWeek = DATEPART(wk,@StartDate) -- CORRECT BY DATEFIRST 6
        SET @StartDay = (DATEPART(dw,@StartDate) + @@DATEFIRST - 1 - 1) % 7 + 1 -- MONDAY 1
    END