嵌套游标为何无限重复输出同一结果？

我编写了名为sp_GetRackRates的SQL存储过程，通过嵌套游标获取指定酒店的房间信息及各房间不同时段的房价。但运行时，酒店ID、名称和房间信息可正常输出，唯独第一条房价被无限重复打印。

原存储过程代码 #

CREATE PROCEDURE sp_GetRackRates
@HotelID smallint
AS
BEGIN
    DECLARE @HotelName      varchar(30)
    DECLARE @RoomID         smallint
    DECLARE @RoomNumber     smallint
    DECLARE @RTDescription  varchar(200)
    DECLARE @RackRate       smallmoney
    DECLARE @RackRateBegin  date
    DECLARE @RackRateEnd    date

    SELECT @HotelName = HotelName
    FROM Hotel
    WHERE HotelID = @HotelID

    PRINT 'Hotel ID: ' + CAST(@HotelID AS varchar(max)) + ' - ' + CAST(@HotelName as 
    varchar(max))

    PRINT ' '

    SELECT @RoomID = RoomID, @RoomNumber = RoomNumber, @RTDescription = RTDescription
    FROM (Room
    INNER JOIN RoomType ON Room.RoomTypeID = RoomType.RoomTypeID)
    WHERE Room.HotelID = @HotelID


    SELECT @RackRate = RackRate, @RackRateBegin = RackRateBegin, @RackRateEnd = 
           RackRateEnd
    FROM (RackRate
    INNER JOIN Room ON RackRate.HotelID = Room.HotelID)
    WHERE RackRate.HotelID = @HotelID AND RoomNumber = @RoomNumber


    DECLARE cr_GetRoom CURSOR
    FOR 
    SELECT RoomID, RoomNumber, RTDescription
    FROM (Room
    INNER JOIN RoomType ON Room.RoomTypeID = RoomType.RoomTypeID)
    WHERE Room.HotelID = @HotelID

    DECLARE cr_GetRackRates CURSOR
    FOR
    SELECT RackRate, RackRateBegin, RackRateEnd
    FROM (RackRate
    INNER JOIN Room ON RackRate.HotelID = Room.HotelID)
    WHERE RackRate.HotelID = @HotelID AND RoomNumber = @RoomNumber

    OPEN cr_GetRoom

    FETCH NEXT FROM cr_GetRoom
    INTO @RoomID, @RoomNumber, @RTDescription

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Room ' + CAST(@RoomNumber AS varchar(max)) + ': ' + CAST(@RTDescription 
               as varchar(max))

        OPEN cr_GetRackRates
        FETCH NEXT FROM cr_GetRackRates
        INTO @RackRate, @RackRateBegin, @RackRateEnd

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Rate: $' + CAST(@RackRate as varchar(max)) + ' valid ' + 
                   cast(@RackRateBegin AS varchar(max)) + ' to ' + CAST(@RackRateEnd AS 
                   varchar(max))
        END
        CLOSE cr_GetRackRates

        PRINT ' '
    END

    CLOSE cr_GetRoom
    DEALLOCATE cr_GetRackRates
    DEALLOCATE cr_GetRoom
END

示例异常输出 #

HotelID: 2100 - Sunridge B&B

Room 101: Single
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14
Rate: $125.00 valid 2023-03-16 to 2023-11-14

数据库中仅存在一条该房价记录，尝试过使用SELECT DISTINCT但结果未改变。

问题原因 #

1. 内层游标未关联当前房间：内层游标cr_GetRackRates的查询条件用的是外层游标声明前初始化的@RoomNumber变量值，不是当前遍历到的房间号，导致所有房间都查询同一份房价数据。
2. 内层循环无游标移动操作：内层WHILE循环里没有执行FETCH NEXT，一旦@@FETCH_STATUS为0，就会无限循环重复打印同一行数据。
3. 多余的初始化赋值：外层游标声明前的两个SELECT赋值语句完全多余，若查询返回多行，只会保留最后一行的值，对逻辑无帮助。

修复后的存储过程代码 #

CREATE PROCEDURE sp_GetRackRates
@HotelID smallint
AS
BEGIN
    DECLARE @HotelName      varchar(30)
    DECLARE @RoomID         smallint
    DECLARE @RoomNumber     smallint
    DECLARE @RTDescription  varchar(200)
    DECLARE @RackRate       smallmoney
    DECLARE @RackRateBegin  date
    DECLARE @RackRateEnd    date

    SELECT @HotelName = HotelName
    FROM Hotel
    WHERE HotelID = @HotelID

    PRINT 'Hotel ID: ' + CAST(@HotelID AS varchar(max)) + ' - ' + CAST(@HotelName as varchar(max))
    PRINT ' '

    -- 外层房间游标
    DECLARE cr_GetRoom CURSOR
    FOR 
    SELECT RoomID, RoomNumber, RTDescription
    FROM Room
    INNER JOIN RoomType ON Room.RoomTypeID = RoomType.RoomTypeID
    WHERE Room.HotelID = @HotelID

    OPEN cr_GetRoom
    FETCH NEXT FROM cr_GetRoom INTO @RoomID, @RoomNumber, @RTDescription

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Room ' + CAST(@RoomNumber AS varchar(max)) + ': ' + CAST(@RTDescription as varchar(max))

        -- 内层房价游标：每次循环重新声明，关联当前房间号
        DECLARE cr_GetRackRates CURSOR
        FOR
        SELECT RackRate, RackRateBegin, RackRateEnd
        FROM RackRate
        INNER JOIN Room ON RackRate.HotelID = Room.HotelID AND RackRate.RoomID = Room.RoomID -- 用RoomID关联更精准
        WHERE RackRate.HotelID = @HotelID AND Room.RoomNumber = @RoomNumber

        OPEN cr_GetRackRates
        FETCH NEXT FROM cr_GetRackRates INTO @RackRate, @RackRateBegin, @RackRateEnd

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Rate: $' + CAST(@RackRate as varchar(max)) + ' valid ' + cast(@RackRateBegin AS varchar(max)) + ' to ' + CAST(@RackRateEnd AS varchar(max))
            -- 必须添加此行，让游标移动到下一条记录
            FETCH NEXT FROM cr_GetRackRates INTO @RackRate, @RackRateBegin, @RackRateEnd
        END

        CLOSE cr_GetRackRates
        DEALLOCATE cr_GetRackRates -- 内层游标用完及时释放

        PRINT ' '
        -- 外层游标移动到下一个房间
        FETCH NEXT FROM cr_GetRoom INTO @RoomID, @RoomNumber, @RTDescription
    END

    CLOSE cr_GetRoom
    DEALLOCATE cr_GetRoom
END

额外优化说明 #

原代码中RackRate与Room仅通过HotelID关联，会导致查询出同酒店所有房间的房价，建议改用RoomID关联，确保每个房间只查询自己对应的房价数据，避免逻辑错误。

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