CODEWITHSUNDEEP
sql-servert-sql
user2360261
user2360261

Reputation: 11

Call a stored proc from another stored proc **without** nesting

I have a stored proc that calls a series of stored procs as part of an account update. Unfortunately I have a 10-minute limit (unrelated to SQL-Server, external timeout) and sometimes it exceeds the timeout. (Under the right conditions it could go an hour.)

I've tried various solutions. The code is about as optimized as it's going to get. One solution would simply be to have an initial stored proc call the "real" stored proc; since the timeout only knows of the initial proc, the real proc can continue unimpeded. The problem is that SQL-Server nests the procs... proc A won't finish until the procs it called (procs B, C and D) are finished.

SQL-Server's internal messaging would work, but our DB isn't compatible. (Out of my control.) I thought about having the initial proc set up a one-time job (to run a minute later) and let the job scheduler run it, but our DBAs probably won't be happy if I'm adding and deleting jobs all the time.

Is there any way for a stored proc to EXEC another stored proc, then immediately quit while the called proc continues to run?

Upvotes: 1

Views: 311

Answers (1)

tommy_o
tommy_o

Reputation: 3783

I've solved this in two ways in the past. One option is to set up Service Broker to send a message into a queue and have, on the other end of Service Broker, execute the second stored procedure. The other alternative is to insert into a queue table and have a SQL agent job run regularly (say, every 5-10 minutes), executing the second stored procedure. Either route, your first sproc will execute, finish the transaction with success, then the second procedure (with it's own transaction) will pick up the queued work and complete it.

For Service Broker, this page will help: http://msdn.microsoft.com/en-us/library/ms345108(v=sql.90).aspx

Either way, you're at some point involving a DBA, but if you have one regular job that executes a sproc against a queue table, you don't need to add and remove jobs all the time, just add and remove work in the queue table (that is, a one-time request from a DBA for setup versus on-going requests from a DBA by adding/removing jobs as OP mentioned).

Upvotes: 2

Related Questions