SQL Server:嵌套光标语法错误

Question

我尝试定义以下SQL Server嵌套光标语句：我循环日历并将它们从cut_calendar迁移到SD_CALENDAR。每个日历也有日历日。每个日历移动也被移动。

 DECLARE @id NUMERIC(20)
    DECLARE @sdCalendarId NUMERIC(20)
    DECLARE @calendarTypId NUMERIC(5)
    DECLARE @name NVARCHAR(35)
    DECLARE @description NVARCHAR(255)
    DECLARE @ptyId NUMERIC(20)
    DECLARE @lockCode NVARCHAR(20)
    DECLARE @dataOwnerId NUMERIC(20)
    DECLARE @cntId NUMERIC(20)
    DECLARE @nonBusinessDaysMonday CHAR(1)
    DECLARE @nonBusinessDaysTuesday CHAR(1)
    DECLARE @nonBusinessDaysWednesday CHAR(1)
    DECLARE @nonBusinessDaysThursday CHAR(1)
    DECLARE @nonBusinessDaysFriday CHAR(1)
    DECLARE @nonBusinessDaysSaturday CHAR(1)
    DECLARE @nonBusinessDaysSunday CHAR(1)
    DECLARE @ccyId NUMERIC(20)
    DECLARE @code NVARCHAR(30)
    DECLARE @version NUMERIC(10)
    DECLARE @seal VARCHAR(255)
    DECLARE @lstUpdTs DATETIME
    DECLARE @day_id NUMERIC(20)
    DECLARE @day_calDate DATETIME
    DECLARE @day_lockCode NVARCHAR(20)
    DECLARE @day_calComment NVARCHAR(255)
    DECLARE @day_dataOwnerId NUMERIC(20)
    DECLARE @day_calendarId NUMERIC(20)
    DECLARE @day_calRecurring CHAR(1)
    DECLARE @day_version NUMERIC(10)
    DECLARE @day_seal VARCHAR(255)
    DECLARE @day_lstUpdTs DATETIME
    DECLARE @day_sdCalendarDaysId NUMERIC(20)
    DECLARE @sdCodeId NUMERIC(20)

    DECLARE cursorCutoffCalendar CURSOR FOR
      SELECT ID, NAME, CALENDAR_TYP_ID,DESCRIPTION,PTY_ID,LOCK_CODE,DATA_OWNER_ID,CNT_ID,NON_BUSINESS_DAYS_MONDAY,NON_BUSINESS_DAYS_TUESDAY,NON_BUSINESS_DAYS_WEDNESDAY,NON_BUSINESS_DAYS_THURSDAY,NON_BUSINESS_DAYS_FRIDAY,NON_BUSINESS_DAYS_SATURDAY,NON_BUSINESS_DAYS_SUNDAY,CCY_ID,CODE,VERSION,SEAL,LST_UPD_TS
      FROM CUT_CALENDAR
      WHERE ID != 1
    OPEN cursorCutoffCalendar
    FETCH NEXT FROM cursorCutoffCalendar INTO @id, @name, @calendarTypId, @description, @ptyId, @lockCode, @dataOwnerId, @cntId, @nonBusinessDaysMonday, @nonBusinessDaysTuesday, @nonBusinessDaysWednesday, @nonBusinessDaysThursday, @nonBusinessDaysFriday, @nonBusinessDaysSaturday, @nonBusinessDaysSunday, @ccyId, @code, @version, @seal, @lstUpdTs
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @sdCalendarId = COALESCE(MAX(ID),1) FROM SD_CALENDAR
        SET @sdCalendarId = @sdCalendarId + 1
        UPDATE CUT_CALENDAR_DAY
        SET CALENDAR_ID = @sdCalendarId
        WHERE CALENDAR_ID = @id
        SELECT @sdCodeId = ID FROM SD_CALENDAR WHERE CODE = @code
        IF @sdCodeId > 0
            SET @code = CONCAT(@code, '_co')
        ELSE
        INSERT INTO SD_CALENDAR (ID, NAME, CALENDAR_ROLE_ID,DESCRIPTION,USE_IN_CUTOFF,PTY_ID,LOCK_CODE,DATA_OWNER_ID,CNT_ID,NON_BUSINESS_DAYS_MONDAY,NON_BUSINESS_DAYS_TUESDAY,NON_BUSINESS_DAYS_WEDNESDAY,NON_BUSINESS_DAYS_THURSDAY,NON_BUSINESS_DAYS_FRIDAY,NON_BUSINESS_DAYS_SATURDAY,NON_BUSINESS_DAYS_SUNDAY,CCY_ID,CODE,VERSION,SEAL,LST_UPD_TS)
        VALUES(@sdCalendarId, @name, @calendarTypId,@description,1,@ptyId,@lockCode,@dataOwnerId,@cntId,@nonBusinessDaysMonday,@nonBusinessDaysTuesday,@nonBusinessDaysWednesday,@nonBusinessDaysThursday,@nonBusinessDaysFriday,@nonBusinessDaysSaturday,@nonBusinessDaysSunday,@ccyId,@code,@version,@seal,@lstUpdTs)
        DECLARE cursorCutoffCalendarDays CURSOR FOR
          SELECT ID, CAL_DATE, LOCK_CODE,CAL_COMMENT,DATA_OWNER_ID,CALENDAR_ID,CAL_RECURRING,VERSION,SEAL,LST_UPD_TS
          FROM CUT_CALENDAR_DAY
          WHERE ID != 1
        OPEN cursorCutoffCalendarDays
        FETCH NEXT FROM cursorCutoffCalendarDays INTO @day_id, @day_calDate, @day_lockCode, @day_calComment, @day_dataOwnerId, @day_calendarId, @day_calRecurring, @day_version, @day_seal, @day_lstUpdTs
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @day_sdCalendarDaysId = COALESCE(MAX(ID),1) FROM SD_CALENDAR_DAY
            SET @day_sdCalendarDaysId = @day_sdCalendarDaysId + 1
            INSERT INTO SD_CALENDAR_DAY (ID, CAL_DATE, LOCK_CODE,CAL_COMMENT,DATA_OWNER_ID,CALENDAR_ID,CAL_RECURRING,VERSION,SEAL,LST_UPD_TS)
            VALUES(@day_sdCalendarDaysId, @day_calDate, @day_lockCode, @day_calComment, @day_dataOwnerId, @day_calendarId, @day_calRecurring, @day_version, @day_seal, @day_lstUpdTs)

        FETCH NEXT FROM cursorCutoffCalendarDays INTO @day_sdCalendarDaysId, @day_calDate, @day_lockCode, @day_calComment, @day_dataOwnerId, @day_calendarId, @day_calRecurring, @day_version, @day_seal, @day_lstUpdTs
        END
        CLOSE cursorCutoffCalendarDays
        DEALLOCATE cursorCutoffCalendarDays
        GO
        DELETE FROM CUT_CALENDAR
        WHERE ID = @id
    FETCH NEXT FROM cursorCutoffCalendar INTO @sdCalendarId, @name, @calendarTypId, @description, @ptyId, @lockCode, @dataOwnerId, @cntId, @nonBusinessDaysMonday, @nonBusinessDaysTuesday, @nonBusinessDaysWednesday, @nonBusinessDaysThursday, @nonBusinessDaysFriday, @nonBusinessDaysSaturday, @nonBusinessDaysSunday, @ccyId, @code, @version, @seal, @lstUpdTs
    END
    CLOSE cursorCutoffCalendar
    DEALLOCATE cursorCutoffCalendar
    GO

现在运行此操作时，我会收到以下错误：

sqlserverexception：近附近的语法不正确。

cursorcutoffcalendardays是我的语句的内部光标。

Answer 1

尝试删除从：

    CLOSE cursorCutoffCalendarDays
    DEALLOCATE cursorCutoffCalendarDays
    GO

我同意您从Garethd获得的评论，我曾经写过这样的光标，但后来我问了如何在没有光标的情况下做到这一点，并得到了一个很好的解释

Answer 2

blockquote

从此线中删除GO，然后尝试

 CLOSE cursorCutoffCalendarDays
 DEALLOCATE cursorCutoffCalendarDays