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

rickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.