Why does the nested cursor keep pulling the same result infinitely

I have a procedure that has a nested cursor to return the rooms of the hotel, with a nested cursor inside that cursor to return the cost for that room over the different periods of time. However, when I run my procedure, I get the hotel id and name (as I should), the room name and description (as I should), but then the first cost value gets infinitely printed out.

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

A Sample output looks as follows:

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

Where the Rate: $125.00 continues infinitely, even though there is only one of those values in the database.

Why does the nested cursor keep repeating the same value? I've tried using SELECT DISTINCT but it kept giving the same results.

I figured out what my issue was.

In the blocks

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

I was not fetching the next item, so it was just sitting at the same item constantly returning it.

It should be:

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))
            FETCH NEXT FROM cr_GetRackRates
            INTO @RackRate, @RackRateBegin, @RackRateEnd
        END
        CLOSE cr_GetRackRates

        PRINT ' '
        FETCH NEXT FROM cr_GetRoom
        INTO @RoomID, @RoomNumber, @RTDescription
    END

You seem to mix cursors with regular variable fetching and not doing actual loops, and declaring the cursors in wrong position so i'm gonna assume your fixed code doesn't work, so please forgive me if my assumption is not correct.

The improved(?) version:

CREATE PROCEDURE spGetRackRates
@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 READ_ONLY FORWARD_ONLY LOCAL STATIC FOR 
		SELECT	RoomID, RoomNumber, RTDescription
		FROM	Room
		INNER JOIN RoomType ON Room.RoomTypeID = RoomType.RoomTypeID
		WHERE	Room.HotelID = @HotelID

	OPEN cr_GetRoom
		
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM cr_GetRoom
		INTO @RoomID, @RoomNumber, @RTDescription
		
		IF @@FETCH_STATUS <> 0
			BREAK
			
		DECLARE cr_GetRackRates CURSOR READ_ONLY FORWARD_ONLY LOCAL STATIC FOR
			SELECT	RackRate, RackRateBegin, RackRateEnd
			FROM	RackRate
			INNER JOIN Room ON RackRate.HotelID = Room.HotelID
			WHERE	RackRate.HotelID = @HotelID AND RoomNumber = @RoomNumber
		
		OPEN cr_GetRackRates
		
		WHILE 1 = 1
		BEGIN
			FETCH NEXT FROM cr_GetRackRates
			INTO @RackRate, @RackRateBegin, @RackRateEnd

			IF @@FETCH_STATUS <> 0
				BREAK
				
			PRINT 'Room ' + CAST(@RoomNumber AS varchar(max)) + ': ' + CAST(@RTDescription as varchar(max))
			PRINT 'Rate: $' + CAST(@RackRate as varchar(max)) + ' valid ' + 
		           cast(@RackRateBegin AS varchar(max)) + ' to ' + CAST(@RackRateEnd AS 
		           varchar(max))
		END
		PRINT ' '
		CLOSE cr_GetRackRates
		DEALLOCATE cr_GetRackRates
	END
	CLOSE cr_GetRoom
	DEALLOCATE cr_GetRoom
	
END

A couple of points:

1. Unless you want to update data, you should declare your cursors as local static, which avoids pollution the namespace of cursors, and also improve your performance.
2. You have to declare the inner cursor "inside" the loop after your fetch the room number, otherwise it just becomes static bound to the original room variable.
3. I prefer to use WHERE 1 = 1 loops and check for fetch_status after the fetching of variables. This is because a lot of times one forgets to fetch the variables again in the end of the loop which messes things up.