• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 35
  • Last Modified:

SP that executes 2 other SPs

Is it possible to have 1 SP execute 2 other SPs simultaneously?

Looking to have both SPs execute at the same time.  Not run one, then other runs after.

Is that possible?

Something like this...?
CREATE PROCEDURE SP_DEMO
AS
SET NOCOUNT ON


EXEC SP_1 @SQL, @TABLE_NAME
GO
EXEC SP_2 
GO

Open in new window

0
rick
Asked:
rick
  • 4
  • 2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No.

What you can do is bundle them both in a transaction such that they succeed or fail together.

Or, you can write your own SP that does the actions of both.
0
 
Mark WillsTopic AdvisorCommented:
Concurrent execution ? Not within SQL Server.

Jim has offered some good ideas, otherwise you would need to submit to some external process where you are not waiting on the results.

Hmmm... Would be interesting.....
0
 
Mark WillsTopic AdvisorCommented:
There is sp_start_job that could be used to start a process. Let me think a bit more about it. Could be a service broker option too....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark WillsTopic AdvisorCommented:
Okay, because your SP2 doesnt need parameters, set it up as a job, then use https://msdn.microsoft.com/en-us/library/ms186757(v=sql.110).aspx to launch immediately and control is returned to your SP where you can run SP1 (complete with params)

You may not really know what happened to SP2 unless you modify that procedure to update / generate status in a DB repository.

Service Broker is a lot more involved but might also work for you. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker


Just found this : https://www.codeproject.com/Articles/541834/How-to-execute-multiple-long-running-SQL-Statement
0
 
rickAuthor Commented:
thank you both for your input.  Confirmed what I thought.
0
 
rickAuthor Commented:
Thank you both for your help.  Confirmed what I thought.
0
 
Mark WillsTopic AdvisorCommented:
A pleasure, let us know how you go. Good luck and thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now