What does @@FETCH_STATUS <>-1 AND @@FETCH_STATUS <>-2 mean?

Question

I came across an examination question, which had the following code in it:

ALTER FUNCTION fncF2 (@HWStreamIndex int = 0)
RETURNS nvarchar(max)
AS
BEGIN

    DECLARE @OnSiteGroups AS nvarchar(150)
    DECLARE @OnSiteGroupDetails AS nvarchar(1000)
    DECLARE @HWOnSiteGroupIndex AS int
    DECLARE @OnSiteGroupsTotal AS nvarchar(max)
    SET @OnSiteGroupsTotal = ''

    DECLARE cursor1 CURSOR
    READ_ONLY FOR
            SELECT
                HWOnSiteGroupName + '-' AS OnSiteGroups,
                HWOnSiteGroupIndex
            FROM 
                HW_STREAM_ONSITE_GROUP
            WHERE 
                HWStreamIndex = @HWStreamIndex

    OPEN cursor1

    FETCH NEXT FROM cursor1 INTO @OnSiteGroups, @HWOnSiteGroupIndex
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            BEGIN
                SET @OnSiteGroupsTotal = @OnSiteGroupsTotal + @OnSiteGroups + ' -- '

                DECLARE cursor2 CURSOR
                READ_ONLY FOR
                        SELECT
                            ISNULL(HW_MANAGE_METH_CODES.HWMMCode, '') + '_' +
                            CONVERT(nvarchar(20), CONVERT(decimal(12, 2), 
                            ISNULL(HW_STREAM_ONSITE_GROUP_DETAILS.PercentProcessed, 0.0) * 100)) + '%' AS OnSiteGroups
                        FROM 
                            HW_STREAM_ONSITE_GROUP_DETAILS
                            JOIN HW_STREAM_ONSITE_GROUP
                                ON HW_STREAM_ONSITE_GROUP_DETAILS.HWOnSiteGroupIndex = HW_STREAM_ONSITE_GROUP_DETAILS.HWOnSiteGroupIndex
                            LEFT JOIN HW_MANAGE_METH_CODES
                                ON HW_STREAM_ONSITE_GROUP_DETAILS.HWMMCodeIndex = HW_MANAGE_METH_CODES.HWMMCodeIndex
                        WHERE 
                            HW_STREAM_ONSITE_GROUP_DETAILS.HWOnSiteGroupIndex = @HWOnSiteGroupIndex

                OPEN cursor2

                FETCH NEXT FROM cursor2 INTO @OnSiteGroupDetails
                WHILE (@@fetch_status <> -1)
                BEGIN
                    IF (@@fetch_status <> -2)
                    BEGIN
                        BEGIN
                            SET @OnSiteGroupsTotal = @OnSiteGroupsTotal + @OnSiteGroupDetails + ', '
                        END
                    END
                    FETCH NEXT FROM cursor2 INTO @OnSiteGroupDetails
                END

                CLOSE cursor2
                DEALLOCATE cursor2

            END
        END
        FETCH NEXT FROM cursor1 INTO @OnSiteGroups, @HWOnSiteGroupIndex
    END

    CLOSE cursor1
    DEALLOCATE cursor1

    RETURN @OnSiteGroupsTotal
END

Usually I use @@FETCH_STATUS = 0 while using cursors. I tried to understand how this code works, and how @@FETCH_STATUS <> -1 and @@FETCH_STATUS <> -2 affects the overall execution, but I was not able to get a grip on this code.

Can somebody explain me what is the purpose of the statements @@FETCH_STATUS <> -1 and @@FETCH_STATUS <> -2 and what are the execution steps of this function?

Answer 1

As explained in the Microsoft Documentation, there are 4 possible @@FETCH_STATUS values, each which represents a different status message:

• 0 - The FETCH statement was successful.
• -1 - The FETCH statement failed or the row was beyond the result set.
• -2 - The row fetched is missing.
• -9 - The cursor is not performing a fetch operation.

WHILE (@@fetch_status <> -1) is telling the loop to run as long as the last cursor FETCH statement was successful and the row was within the result set.

IF (@@fetch_status <> -2) is checking to make sure that the row exists before continuing with the procedure.