How to run Sql Server stored procedure in Async mode?

Question

Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement.

Service Broker can be one option, but this is 15+ years old technology and Microsoft did not try to enhance its functionality.

Agent job can be an option, but I want to submit a job on demand "as and when required". Plus, the stored proc has parameters as well.

I came from Oracle background and there are multiple ways to submit a background job to the Database engine and it will run the job in Async manner.

• Aaron Bertrand: Have you considered just using a queue table? Also don’t discount service broker because it hasn’t been enhanced. It just works. Log shipping is older and it still works perfectly fine. Agent hasn’t really improved in all that time either. Just because it isn’t shiny doesn’t make it crap.
• snaseer: Thanks @AaronBertrand. I'm relatively new for Sql Server. So far I'm aware of Service Broker and Agent job only. I 'm religiously following BrentOzar and he thinks doing anything in Service Broken is mistake. As far Agent jobs is concerned I will need to find way to pass parameter and run on demand. Queue Table and/or Log Shipping are new info for me, If possible please point me to example or intro for these. In my case when a row is inserted in TabA then job will be submitted and that job will take PkID of TabA as param and do expensive Ins/Upd that not immediately needed. Thanks again
• Jonathan Fite: Create a queue table with details / arguments for process. Application inserts details into queue table. Then launches Agent job through TSQL (this is async) (or Agent job runs every X minutes). Agent job reads queue table details and does X. Service broker is the better approach for this kind of thing if you want it all in-engine. From personal experience it can work great, I would just limit yourself to a few hundred messages per minute at most.

Answer 1

The simplest way is to call sp_start_job and kick off an unscheduled SQL Agent Job. Parameter values can be stored in a table that the job reads from before calling the procedure.