August 8, 2006 at 4:16 pm
I have a procedure, the general structure of which is this:
declare cursor
while(@@fetchstatus <> -1)
...
declare cursor
while(@@fetch_status <> -1)
...
declare cursor
while(@@fetch_status <> -1)
...
In attempting to isolate a problem, I split the procedure into three separate procedures. After isolating the problem to cursor 3, I made corrections to the query and pasted it back into the main procedure. Since then, cursor 2 does not return data. If I run the query manually, I get all the rows I should. If I run the previously split procedure (which had just been copied and pasted) it runs properly.
This seems mind boggling to me.
I have put the two procedures on the web at http://www.visualdistortion.org/picdump/images/open_items.sql and http://www.visualdistortion.org/picdump/images/open_items_close_rent.sql
The part that doesn't work is the cursor "close_cursor".
Any thoughts would be appreciated.
Thanks
August 8, 2006 at 4:33 pm
Your biggest problem is that, not only are cursors inefficient, you are using nested cursors. I would suggest finding a set based solution to your problem. I will take a look at your SQL code this evening or tomorrow morning as time allows.
August 8, 2006 at 4:42 pm
are these cursors nested within each other?, it might be your issue is you are using @@fetch_status as the bailout condition for all 3 nested loops; as soon as one of the loops returns no data, it bails the whole thing out;
when nesting like this, you need to put the fetch status in a local variable, and use that for the loop condition.
try something like this:
declare @dbname varchar(20),@whatever varchar(30)
declare @innerloop int,@middleloop int,@outerloop int
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
set @outerloop=@@fetch_status
While @outerloop <> -1
begin
--do work
declare c2 cursor for [something else]
open c2
fetch next from c2 into @dbname
set @middleloop=@@fetch_status
While @middleloop <> -1
begin
--do work
[next inner loop goes here
fetch next from c2 into @whatever
set @middleloop=@@fetch_status
end
close c2
deallocate c2
fetch next from c1 into @dbname
set @outerloop=@@fetch_status
end
close c1
deallocate c1
Lowell
August 8, 2006 at 7:39 pm
The cursors are not nested; each one finishes before the next one starts.
August 10, 2006 at 10:19 am
Inside the loop that processes close_cursor, there are two stored procedure calls:
EXEC dbo.usp_Close_Open_Item @LOI_Open_Item_Amt, @LOI_Lease_Open_Item_Id, 0
EXEC dbo.usp_Close_Open_Item @LOI_Open_Item_Amt, @LOI_Lease_Open_Item_Id, @SD_CRCCPP
Do either of those stored procedures use cursors? As Lowell mentioned, @@FETCH_STATUS is shared among all cursors.
Also, I would use WHILE @@FETCH_STATUS = 0, since -2 is also an error condition that you will process if it occurs.
August 10, 2006 at 10:28 am
Nope, they're just a couple inserts and an update.
August 11, 2006 at 11:41 am
I finally figured it out. I just had to make sure my open was called in the same transaction the work was being done in. (Before I was doing open; begin transaction; while() {}; commit transaction.
What a lot of effort for a silly solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply