Sql-server – SQL Server Deferred Stored Procedure Execution

sql serversql server 2014stored-procedures

I have a stored procedure (e.g. SP_DoSomething) with 2 parameters. Inside the code of this SP there's another SP execution (e.g. SP_DoSomethingElse) with an enormous list of parameters like this:

CREATE PROCEDURE SP_DoSomethingElse
(
    @param1 int,
    ...
    @paramN int
)
AS
BEGIN
    EXEC SP_DoSomethingElse @param1, ... @paramN
END

What I want to achieve is to defer the execution of SP_DoSomethingElse for later execution.

Is there a way to save somewhere (and somehow) the SP execution context (state), including all the parameters and their values?

I prefer not to concatenate as varchar the whole exec.

I found this similar question but is related to replication:

Publishing stored proc execution in transactional replication

If SP_DoSomething is called and there is already a pending SP_DoSomethingElse, no special handling is needed because the calls are not related, and they'll just go into a queue.

This is on SQL Server 2014, but with backward compatibility if possible, no matter if not.

Best Answer

How often is this stored procedure called?

One way to do this would be to use a table to track the times/parameters you want to execute. Create a table with a datetime to execute the stored procedure at, and an active flag.

Every time SP_DoSomething is executed, it inserts values into this table. Run a scheduled job every few minutes to check the table for Datetime values less than GETDATE(), run the stored procedure and change the active flags.

To store the parameters, you would need columns in your table, one for each parameter that you'll be using. Instead of executing SP_DoSomethingElse, place those values into a table.

Related Question