One pattern to solve this is to queue all
the events calls, and have the top level cursor iterating through the queue. As
stored procedures get called, they can add to the queue, and the top level
cursor will execute the queued stored proc at the end of the currently
executing set.
Note that this does change the order of
execution of stored procedures though (it moves the nested proc calls to the
end of the non nested calls), and so might not be appropriate for your use.
In SQL, do this by using a temporary table
on the connection.
Check
for its existence in the usp_ProcessStatusChange,
if it exists, just add
the new events to it, if not, create it, populate it and start iterating. Make
sure that the ordering of the iteration guarantees that new items get added at
the end (e.g. identity column)
I’d be interested what other people
think of that approach, pros and cons.
Yours,
Martin Granell
Senior Consultant
Readify
From:
SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx]
On Behalf Of David Ames
Sent: Friday, 4 March 2005 12:42
PM
To:
SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] SProc
Recursion and A cursor with the name 'eventCursor' already exists error.
Hi All,
I'm currently working on a ticking system with a fairly
complex status tracking sub-system.
Essentially, when a status changes, we fire a stored
procedure to handle updates to several tables. (usp_ProcessStatusChange)
Business rules dictate that when a status changes, 0..n
'events' can fire. These events are really just more stored
procedures. We fire them by setting up an events cursor, then EXECing the
event stored procedure.
This system works really well, except I now find myself
having to code an event that itself calls usp_ProcessStatusChange, ie, a
recursive operation. - (I am aware that SQL limits recursion to 16 levels
deep).
Logically it looks something like this
usp_ProcessStatusChange
Setup EventCursor
While Fetch_Status = 0
EXEC
usp_MyNewEvent
LoopEventCursor
CloseDeallocte EventCursor
usp_MyNewEvent
Grab a related job ticket
Do some processing on it
EXEC usp_ProcessStatusChange
My problem is that when usp_MyNewEvent calls
usp_ProcessStatusChange my connection already has a cursor named EventCursor,
and thus I receive a A cursor with the name 'eventCursor' already
exists error.
I currently can't see a way around this and I'm hoping one
of the experts on this list can suggest a different approach for this.
Thanks,
Dave
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
