Nesting Cursors
-
I'm developing a complex T-SQL procedure which uses nested cursors; or at least tries. Using the debugger it appears that @@FETCH_STATUS = 0 for the first cursor and reads the first row; then the nested cursor runs and reads all the rows. When the nested cursor is complete @@FETCH_STATUS becomes -1 which is the value when it tries to read the second row of the unnested cursor. Can nested cursor be run or would it be necessary to build a second procedure which is executed from within the first? Any recommendations would be appreciated.
Steve King
-
I am aware that @@FETCH_STATUS is a global variable, after a while using BOL, and when the nested cursor updates @@FETCH_STATUS to -1 the unnested cursor sadly claims it as it's own. My real question is how do I implement a design that will work?
-
When using nested cursors, you end up being dependent on when you read your cursor in the loop. For example:
declare c_1 CURSOR FOR
SELECT Field1 From Tbl1
Declare @F1 int
Declare @F2 varchar(20)
Open c_1
FETCH NEXT FROM c_1 INTO @F1
WHILE @@FETCH_STATUS = 0
BEGIN
Declare c_2 CURSOR FOR
Select TxtFld From Table2
WHERE txtID = @F1
OPEN c_2
FETCH NEXT FROM c_2 INTO @F2
WHILE @@FETCH_STATUS = 0
BEGIN
Print @F2
FETCH NEXT FROM c_2 INTO @F2
END
-- At this point, @@FETCH_STATUS <> 0
CLOSE c_2
DEALLOCATE c_2
FETCH NEXT FROM c_1 INTO @F1
-- at this point, @@FETCH_STATUS = 0 if new data was fetched
END
CLOSE c_1
DEALLOCATE c_1So, if you code your loops with the read at the top of the loop (as I have seen some folk do), then the @@FETCH_STATUS value is retained from the inner loop, and the outer loop exits, even though the outer loop is not complete. However, if you code it as above (a 'priming read' before the loop, and additional reads at the end of the loop) then the @@FETCH_STATUS is correct for the given loop whenever it is checked.
-
Assign the value of @@FETCH_STATUS to a local variable and use that in your logic
eg:
FETCH NEXT FROM Outer blah...blah...
SET @Outer_loop = @@FETCH_STATUS
WHILE @Outer_loop = 0
BEGIN
FETCH NEXT FROM Inner blah...blah...
Set @inner_loop = @@FETCH_STATUS
WHILE @Inner_loop = 0
BEGIN
do some stuff
FETCH NEXT FROM inner blah...blah...
Set @inner_loop = @@FETCH_STATUS
END
FETCH NEXT FROM Outer blah...blah...
SET @Outer_loop = @@FETCH_STATUS
ENDHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 09/25/2003 6:24:05 PM
--------------------
Colt 45 - the original point and click interface
-
Thanks! You recommendations helped me overcome this issue. I wasn't using a priming fetch. Now it works great. Documentation was really spotty and didn't explain the need for it. Having used Recordsets in Visual Basic I was unprepared for this.
Steve King
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
Ok... since SPAM keeps necroing this 2 decade old thread and someone might read it and without knowing what the cursors are actually being used for, I'll just up and say that using Nested Cursors or even Single Cursors is usually the worst way possible to do anything except for "flow control" and even that might be able to be repaired by fixing the stuff in the flow.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And, wow... Phil Carter. Were did you ever get off to?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply