Executing a stored procedure within a stored procedure

Question

Hi,

I would like to execute a stored procedure within a stored procedure eg

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.

Thanks

Answer 1

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

exec dbo.sp1 exec dbo.sp2

Answer 2

Sorry my question was not clear.

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

CREATE PROCEDURE SP1 AS BEGIN

EXECUTE SP2

END

Answer 3

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)

Answer 4

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'

Answer 5

Hi I have found my problem is that SP2 doesn't execute from within SP1 when SP1 is executed.

Below is the structure of SP1:

ALTER PROCEDURE SP1 AS BEGIN

Declare c1 cursor....

open c1 fetch next from c1 ...

while @@fetch_status = 0 Begin

...

Fetch Next from c1 end

close c1

deallocate c1

exec sp2

end

Answer 6

Your SP2 is probably not running because of some failure in the early code in SP1 so EXEC SP2 is not reached.

Please post your entire code.

Answer 7

Looks like this question is a duplicate. Please only ask your question once.