SQL Server - Executing an Asynchronous Query

Question

In Sql Server 2008, I have a saved proc that writes the result to the output parameter and which inserts the parameters into the table. I want the "insert to table" part to be performed asynchronously so that the result can be read from the output parameter, without waiting for the insert command to complete.

How can i do this?

For instance.

CREATE PROCEDURE dbo.Sample_sp @RESULT INT OUTPUT @PARAM_1 INT, @PARAM_2 INT, @PARAM_N FLOAT AS -- Perform Calculations like @RES = @PARAM_1 + @PARAM_2...... INSERT INTO DBO.A VALUES(@PARAM_1, @PARAM_2, ..... @PARAM_N) 

 EXECUTE ASYNC dbo.Sample_sp 

Answer 1

It is possible (see Executing the asynchronous procedure ), but most likely the results will not be what you want. First of all, the transition to an asynchronous process implies a violation of the transaction context adopted by the caller of the procedure (the insert occurs in another transaction). In addition, running reliable async (as in my related article) involves performing significantly more records, so there is no performance benefit.

Why do you want to start asynchronous work? The cost of an insert is usually not noticeable in the delay in the response, unless it blocks the locks. If you have problems with blocking, contact this address.

Answer 2

You cannot do this in standard SQL: it is synchronous. You also cannot process the output parameters until the stored procedure is complete.

You will need to use a service broker to separate the calculation and INSERT request (Edit: As Remus) with an example)

Note, of course, that now you need more sophisticated error handling to eliminate any errors and rollbacks, because your INSERT will be untied, and you will not receive immediate feedback about any error.

Of course, why not make the calculation in the client first? aka, what is the problem you are really trying to solve ...