SQL Server - Asynchronous Query Execution

Question

In Sql Server 2008, I have a stored proc that writes the result into an output parameter and that inserts the parameters into a table. I want to make the "inserting into a table" part of the SP to run asynchronously so that the result can be read from the output parameter without waiting the insert command complete.

How can I do it?

For example.

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 Asynchronous procedure execution), but very likely the results will not be what you want. First and foremost going async implies breaking the transactional context assumed by the caller of the procedure (the insert occurs in a different transaction). Also, doing a reliable async (as in my linked article ) implies doing significantly more writes, so there is no performance benefit.

Why do you want to go async to start with? The cost of an insert is usually not noticeable in the response latency unless it blocks on locks. If you have locking contention, address that issue.

Answer 2

You can't do it in standard SQL: it is synchronous. You also can't process output parameters until the stored procedure has completed.

You'd have to use service broker to decouple the calculation and the INSERT (Edit: As per Remus' answer with example)

Note of course that you now need more complex error handling to deal with any errors and rollbacks because your INSERT would be decoupled and you don't get immediate feedback of any error.

Of course, why not do the calculation in the client first? a.k.a what is the problem you're really trying to solve...