recursive stored procedure w/cursor?

[BeanDog]

I know it sounds kind of ridiculous, but nevertheless I do have to have a stored procedure that calls itself. It has a cursor active in it when it calls itself. When the second instance of the procedure runs, it gives me (obviously) a cursor already exists error. Can anyone think of a way around this?

Here's the full code:

Alter Procedure qrysMaxOverlaps
(
@pAppointmentID int,
@pOverlaps int OUTPUT
)
As
/*
This query returns the maximum number of appointments that begin during this one.
*/
declare @sTime datetime
declare @eTime datetime
declare @Master int
declare @date datetime
declare @tempID int
declare @tempint int
declare cur CURSOR FOR
SELECT starttime, endtime, masterID, date FROM tblsAppointment WHERE appointmentID = @pAppointmentID

OPEN cur
FETCH cur INTO @sTime, @eTime, @Master, @date
CLOSE cur
DEALLOCATE cur

/*
we have to loop through all the appointments that start during this one and call this
procedure. Whenever one produces a higher value, @pOverlaps will be set to that value.
*/
declare cur CURSOR FOR
SELECT appointmentID FROM tblsAppointment
WHERE(
masterID=@Master AND
date=@date AND
starttime > @sTime AND
starttime < @eTime
)

OPEN cur

set @tempID = 0
set @tempint = 0
set @pOverlaps = 0
FETCH cur INTO @tempID
while (@@FETCH_STATUS=0)
BEGIN
FETCH cur INTO @tempID
EXECUTE qrysMaxOverlaps @tempID, @tempint
if @tempint > @pOverlaps
BEGIN
set @pOverlaps = @tempint
END
END
CLOSE cur
DEALLOCATE cur


return


~BenDilts( void );

~BenDilts( void );
benbeandogdilts@cs.com

http://www.geocities.com/benbeandogdilts

Long-time BASIC game programmer, Internet programmer and C++/DirectX of late.

 

[jluost1]

Try to declare the cursor as LOCAL. The default is GLOBAL so it exists within the connection session.