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

[David Ames]

Hi All,
 
Thanks for the replies - they really got me thinking...  
 
Anyway, in the end the answer was very simple (as they almost always are).
 
Simply declare the cursor as LOCAL, eg: DECLARE cur_Level CURSOR LOCAL...
 
If anyone is after more info on recursion in SQL, this is a very handy artical:  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asp
 
Regards,
Dave
 

	-----Original Message----- 
	From: Michael van der Veeke [mailto:michaelvanderveeke@xxxxxxxxxxxxxxxxxxxx] 
	Sent: Fri 3/4/2005 12:54 PM 
	To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx 
	Cc: 
	Subject: RE: [SQLDownUnder] SProc Recursion and A cursor with the name 'eventCursor' already exists error.
	
	

	Hello,

	 

	Try adding this before the declare:

	 

	IF Cursor_Status('variable', ' EventCursor ') <= 0

	            Setup EventCursor 

	 

	 

	Otherwise just continue…..should work

	
	Regards, 
	Michael van der Veeke 
	Database Administrator
	Border Express Pty Ltd

	Phone: (02) 6022 6046
	Mobile : 0419 419 547
	Email : michaelvanderveeke@xxxxxxxxxxxxxxxxxxxx
	Web :  http://www.borderexpress.com
	
	NOTICE
	The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it. 

	
  _____  


	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