[SQLDownUnder] SProc Recursion and A cursor with the name 'eventCursor' already exists error.

[David Ames]

Title: Message

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