Menu
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 
756
There's no difference whether you call a procedure from within a procedure or from a pl/sql block; if you can call it from a block, you can call it from a procedure.
Nesting stored procedures means you have stored procedures that call stored procedures; each stored procedure may or may not have a transaction. To trap non-fatal errors in a called stored procedure, the called procedure must have some way to communicate back to the calling procedure that an error has occurred.
In large database applications, it is common to call one stored procedure from another stored procedure. In this blog, I will explain how to execute a stored procedure within another stored procedure in SQL Server. Let's start with creating a stored procedure.
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' 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
