CODEWITHSUNDEEP
sql-servert-sqlwhile-loopcursorfetch
M. Carlo Bramini
M. Carlo Bramini

Reputation: 2522

T-SQL: Cursor iteration not stopping at @@FETCH_STATUS=-1

I’ve created a simple cursor in a stored procedure. The cursor simply iterates all the rows of a table.

From my output it seems that the iterator fetches n+1 rows.

I’ve tried a to break the while loop of the iteration but this doesn’t solve the issue. How come?

ALTER PROC spInsertNewCategory --spInsertNewCategory '-1','-1'
@categoryName NVARCHAR(80),
@categoryInfo NVARCHAR(80)
AS 
BEGIN

    DECLARE @siteLanguage_id      INT
    DECLARE @lang_id              INT
    DECLARE @default_siteLanguage INT

    DECLARE cr_siteLanguage       CURSOR
    FOR 
    SELECT sl.siteLanguage_id, sl.lang_id, sl.default_siteLanguage 
    FROM siteLanguage sl


    OPEN cr_siteLanguage
        FETCH NEXT FROM cr_siteLanguage
        INTO @siteLanguage_id,@lang_id,@default_siteLanguage
        PRINT '@siteLanguage_id: '+ CAST(@siteLanguage_id AS NVARCHAR(3))+' fetch: '+ CAST(@@FETCH_STATUS AS NVARCHAR(5))

        WHILE (@@FETCH_STATUS = 0)
            BEGIN
            IF (@@FETCH_STATUS=-1)
                BEGIN
                PRINT 'in if -1'
                BREAK
                END
            FETCH NEXT FROM cr_siteLanguage
            INTO @siteLanguage_id,@lang_id,@default_siteLanguage
            PRINT '@siteLanguage_id: '+ CAST(@siteLanguage_id AS NVARCHAR(3))+' fetch: '+ CAST(@@FETCH_STATUS AS NVARCHAR(5))
            END
    CLOSE cr_siteLanguage
    DEALLOCATE cr_siteLanguage 

    SELECT * FROM siteLanguage
END

enter image description here enter image description here

Upvotes: 1

Views: 11696

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Yes it is stopping at @@FETCH_STATUS=-1
That is why you are not seeing your PRINT 'in if -1'
On the last FETCH @@FETCH_STATUS=-1 and it exits the WHILE (@@FETCH_STATUS = 0)

No it does not fetch n+1 rows.
On n+1 is when it knows there are no more rows.
The cursor does not know when it is the last row - it only know when there are no more rows (n+1)
That is why you fetch the first row before the loop
You print after the FETCH inside the loop
So you get that final -1
Then it drops out of the WHILE (@@FETCH_STATUS = 0)
Run it in debug and watch 

OPEN cr_siteLanguage
        FETCH NEXT FROM cr_siteLanguage
        INTO @siteLanguage_id,@lang_id,@default_siteLanguage

        WHILE (@@FETCH_STATUS = 0)
            BEGIN
            PRINT '@siteLanguage_id: '+ CAST(@siteLanguage_id AS NVARCHAR(3))+' fetch: '+ CAST(@@FETCH_STATUS AS NVARCHAR(5))
            IF (@@FETCH_STATUS=-1)
                BEGIN
                PRINT 'in if -1'
                BREAK
                END
            FETCH NEXT FROM cr_siteLanguage
            INTO @siteLanguage_id,@lang_id,@default_siteLanguage               
            END
    CLOSE cr_siteLanguage
    DEALLOCATE cr_siteLanguage

Upvotes: 2

user1897277
user1897277

Reputation: 495

It appears that IF (@@FETCH_STATUS=-1) will never be executed when @@FETCH_STATUS=-1,
You are checking the value of @@FETCH_STATUS immediately after entering the loop,
that has a WHERE clause (@@FETCH_STATUS = 0).

you could probably validate after the FETCH NEXT stmt inside the loop.

WHILE (@@FETCH_STATUS = 0)
BEGIN
    FETCH NEXT FROM cr_siteLanguage
    INTO @siteLanguage_id,@lang_id,@default_siteLanguage
    PRINT '@siteLanguage_id: '+ CAST(@siteLanguage_id AS NVARCHAR(3))+' fetch: '+ CAST(@@FETCH_STATUS AS NVARCHAR(5))
    IF (@@FETCH_STATUS=-1)
    BEGIN
        PRINT 'in if -1'
        BREAK
    END
END

Note: I assume the first FETCH NEXT stmt (immediately after OPEN cr_siteLanguage), does retrieve records.

Upvotes: 0

Related Questions