[sql] Executing a stored procedure within a stored procedure

I would like to execute a stored procedure within a stored procedure, e.g.

EXEC SP1

BEGIN

EXEC SP2
END

But I only want SP1 to finish after SP2 has finished running so I need to find a way for SP1 to wait for SP2 to finish before SP1 ends.

SP2 is being executed as part of SP1 so I have something like:

CREATE PROCEDURE SP1
AS
BEGIN

EXECUTE SP2

END

Thats how it works stored procedures run in order, you don't need begin just something like

exec dbo.sp1
exec dbo.sp2

Here is an example of one of our stored procedures that executes multiple stored procedures within it:

ALTER PROCEDURE [dbo].[AssetLibrary_AssetDelete]
(
    @AssetID AS uniqueidentifier
)
AS

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

EXEC AssetLibrary_AssetDeleteAttributes @AssetID
EXEC AssetLibrary_AssetDeleteComponents @AssetID
EXEC AssetLibrary_AssetDeleteAgreements @AssetID
EXEC AssetLibrary_AssetDeleteMaintenance @AssetID

DELETE FROM
    AssetLibrary_Asset
WHERE
    AssetLibrary_Asset.AssetID = @AssetID

RETURN (@@ERROR)

Inline Stored procedure we using as per our need. Example like different Same parameter with different values we have to use in queries..

Create Proc SP1
(
 @ID int,
 @Name varchar(40)
 -- etc parameter list, If you don't have any parameter then no need to pass.
 )

  AS
  BEGIN

  -- Here we have some opereations

 -- If there is any Error Before Executing SP2 then SP will stop executing.

  Exec SP2 @ID,@Name,@SomeID OUTPUT 

 -- ,etc some other parameter also we can use OutPut parameters like 

 -- @SomeID is useful for some other operations for condition checking insertion etc.

 -- If you have any Error in you SP2 then also it will stop executing.

 -- If you want to do any other operation after executing SP2 that we can do here.

END

T-SQL is not asynchronous, so you really have no choice but to wait until SP2 ends. Luckily, that's what you want.

CREATE PROCEDURE SP1 AS
   EXEC SP2
   PRINT 'Done'