Microsoft KB Archive/325187

= PRB: Consecutively Declaring Nested Cursors Immediately Returns a FETCH_STATUS Value of -1 =

Article ID: 325187

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q325187



SYMPTOMS
If you declare a cursor immediately after the declaration of another cursor at the beginning of a procedure, and you then run the cursors in a nested loop, this returns a @@FETCH_STATUS value of –1.



CAUSE
This behavior occurs because nested cursors in SQL Server 7.0, and later, do not take updated values from the outer cursor.

Consider the following example: use pubs go

create proc nik_tst1 as

declare @w_title varchar(10) declare @w_au_id varchar(25) declare titl_cur cursor for select title_id from titles order by title_id declare ta_cur cursor for select au_id from titleauthor where title_id = @w_title open titl_cur fetch titl_cur into @w_title while (@@fetch_status = 0 ) Begin select @w_title open ta_cur fetch ta_cur into @w_au_id select @@fetch_status /* @@FETCH_STATUS value of nested cursor returns -1 when nested cursor is declared before outer cursor's fetch */ while (@@fetch_status = 0 ) Begin select @w_au_id select @w_title, @w_au_id fetch ta_cur into @w_au_id End close ta_cur fetch titl_cur into @w_title End deallocate ta_cur close titl_cur deallocate titl_cur go

exec nik_tst1 go In this example the initial value of @w_title is NULL when you declare the nested cursor. Therefore, every execution of the nested cursor SELECT statement evaluates the value of @w_title to NULL. As a result, the @@FETCH_STATUS value of a nested cursor always returns –1.



WORKAROUND
To work around this problem, declare nested cursors in the WHILE loop of the outer cursor.

Consider the following example: use pubs go create proc nik_tst2 as declare @w_title varchar(10) declare @w_au_id varchar(25) declare titl_cur cursor for select title_id from titles order by title_id open titl_cur fetch titl_cur into @w_title while (@@fetch_status = 0 ) Begin declare ta_cur cursor for select au_id from titleauthor where title_id = @w_title select @w_title open ta_cur fetch ta_cur into @w_au_id select @@fetch_status /* @@FETCH_STATUS value of nested cursor returns 0 when nested cursor is declared in the outer cursor's fetch loop*/ while (@@fetch_status = 0 ) Begin select @w_au_id select @w_title, @w_au_id fetch ta_cur into @w_au_id End close ta_cur deallocate ta_cur fetch titl_cur into @w_title End close titl_cur deallocate titl_cur go

exec nik_tst2 go In this example you declare the inner cursor in the WHILE loop of the outer cursor. Therefore, the SELECT statement of the inner cursor uses the updated value of the @w_title, and it stores the data in the cursor. As a result, the @@FETCH_STATUS of the inner cursor returns 0.

