PRB: Consecutively Declaring Nested Cursors Immediately Returns a FETCH

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.

PRB: Consecutively Declaring Nested Cursors Immediately Returns a FETCH_STATUS Value of -1 (325187)

SYMPTOMS

CAUSE

WORKAROUND

REFERENCES