March 13, 2020 at 4:57 am
Hi all,
I have a stored Procedure that uses a cursor and inside that cursor it kicks of a another stored procedure that also uses a cursor which breaks the @@FETCH_STATUS . how can I use the cursor properly. This worked fine before because the SP was on a diffrent SQL Server Instance and it didn't break @@Fetch_Status.
OPEN First_cur
FETCH next FROM First_cur INTO @idRmatch
select @id2 = id
from database.dbo.table2
where idRmatch=@idRmatch (this is used to get parameters for SP)
exec storedprcoedure1 @id
exec storedprocedure2 @id (this uses a cursor insisde which breaks the cursor)
How can I go around this?
Thanks, I know its a bad design.
March 13, 2020 at 5:47 am
Because @@FETCH_STATUS is global to all cursors on a connection, use it carefully. After a FETCH statement executes, the test for @@FETCH_STATUS must occur before any other FETCH statement executes against another cursor. @@FETCH_STATUS is undefined before any fetches have occurred on the connection.
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes results from another cursor. When control returns from that called stored procedure, @@FETCH_STATUS reflects the last FETCH executed inside that stored procedure, not the FETCH statement executed before the call to the stored procedure.
To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic management function.
declare @i int
declare @j int
declare cur_outer cursor local for
select i from (values (1), (2), (3)) x(i)
open cur_outer
fetch cur_outer into @i
while (select fetch_status from sys.dm_exec_cursors(0) cs where cs.name = 'cur_outer') = 0
begin
print @i
declare cur_inner cursor local for
select j from (values (1), (2), (3)) y(j)
open cur_inner
fetch cur_inner into @j
while @@FETCH_STATUS = 0
begin
print @j
fetch cur_inner into @j
end
close cur_inner
deallocate cur_inner
fetch cur_outer into @i
print '============================='
end
close cur_outer
deallocate cur_outer
I Have Nine Lives You Have One Only
THINK!
March 13, 2020 at 10:26 am
Cursors in cursors? Serious anti-pattern here. I'd strongly recommend reassessing the approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2020 at 2:50 pm
Hold the phone a minute... if this worked on a different instance, you have to ask yourself what changed when you copied it to the new instance if it doesn't work on the new instance. These things don't break for no reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply