Nested Stored Procedure Connections

Question

I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging table if there is a newer file.

At this point, if it does import a newly added file, there is another accompanying SP that ETL's the records from staging into another production DB. The ETL (EXEC dbo.ETL) is happening inside the monitoring SP that runs every 5 minutes.

My main question is, can I have the ETL stored Procedure run independently of the monitoring SP? If an ETL SP takes longer than 5 mins, when the monitoring SP is called again, will it use another connection if the last call 5 mins ago is still running the ETL procedure?

IOW, I want to run a SP (SP #1) every 5 mins that calls other SPs (that may take more or less than 5 mins) without interrupting SP #1

Answer 1

A solution could be to build a simple SSIS package that runs the stored procedure you need; then call it from tsql in this way:

declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'folder'
 ,@project_name = 'project_name'
 ,@package_name = 'run_sp.dtsx'
 ,@execution_id = @execution_id output

 exec ssisdb.catalog.start_execution @execution_id

This call is asyncronous by default

Answer 2

Are you asking of you can call some other stored procedure asynchronously? No, not natively, so to speak. We usually have two options for this:

Execute an Agent job, using sp_start_job. Note that an agent job doesn't have parameters so if you want to pass data into this job, so to speak, you can use a table as a communication mechanism. Note that if the ETL is running (through a started Agent job) and your monitoring proc tries to start it again, then it won't be started. Agent will not start a job if it's already running.

Use Service Broker. This is queuing in SQL Server, so your "outer" proc can send a message to a queue, and you can have either a polling mechanism to poll the queue or activation (Service Broker kicks off something when a message is received).

Or did you mean something different? If so, please clarify. Your last sentence is a bit confusing since you essentially say that you want to run sp #1 without interrupting sp #1.