Returning from a stored procedure before it is finished executing

Question

We have a stored procedure in which there are two parts. In part 1, failures need to be returned to the application, and the part must be completely executed before the stored procedure returns.

Part 2 is a long process which needs to execute whenever the procedure is called, but all failures can be ignored (or handled elsewhere), however we want the application to return before it is completed. SO we need a procedure that does this:

exec PART1
return
exec PART2 -- still gets executed, but after sql server has already responded that the SP was a success to the client.

I thought we could potentially do this as a 'worker thread', though I don't know any syntax to do this in SQL server.

Any ideas? Am I on the right track? Do you know the syntax to accomplish this? Am i overlooking some sort of easier way to accomplish this?

Answer 1

Never heard this was possible in SQL. It wants to process a batch via SPs.

Digging around though, we find this:

When using the SQL Server Native Client OLE DB provider, applications designed to initialize a data source object asynchronously can set the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_INIT_ASYNCH property prior to calling IDBInitialize::Initialize.

This sounds like you're choosing the path of pain. What if you called the Stored Proc via .NET and based on the condition set in PART1, your application would decide to run PART2 or not?

Answer 2

Something you could look into is Implementing Jobs in SQL Server.

The idea would be to have two stored procedures, one of which is called by your program, and one of which is called by a job. The final action of the first would be to run (or schedule) the job. See also Running Jobs

Answer 3

You could combine a stored procedure for your first part and a trigger for second.

Answer 4

There are three standard ways to do what you're asking:

1. Queue a job to SQL Agent
2. Use Service Broker activation to kick off another SP (probably your best bet)
3. Use Service Broker messaging to send a message to a Windows Service, which then kicks off the desired SP

Answer 5

See Asynchronous procedure execution for an example how to do this w/o the involvement of the SQL agent scheduler. The SQL Agent has a number of draw backs: scalability, concurency, failover scenarios, database backup/restore consistency and not the least unavailability in Express editions. The solution in the link overcomes all these problems by leveraging the Service Broker Activation mechanism.