How to execute stored procedures by Order in SQL Server

Question

I have created a stored procedure which calls other stored procedures but I don't want to execute them at the same time. Stored procedure #1 execution takes time so when it finishes stored procedure #2 will start to execute.

How can I do this?

EXEC SP1;
--wait for SP1 to finish its job
EXEC SP2;

Any suggestions?

Answer 1

Try using something like :

BEGIN TRY
BEGIN TRANSACTION
exec( @sp1)
exec(@sp2)
exec(@sp3)
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 (Validate it )
ROLLBACK (rollback to sp where you want to )
END CATCH

Answer 2

It should be as simple as

EXEC SP1;
GO
EXEC SP2;
GO