Ausführen einer gespeicherten Prozedur innerhalb einer gespeicherten Prozedur

Ich möchte eine gespeicherte Prozedur innerhalb einer gespeicherten Prozedur ausführen, zB

EXEC SP1 BEGIN EXEC SP2 END 

Aber ich will nur SP1 beenden, nachdem SP2 fertig läuft, also muss ich einen path für SP1 , um auf SP2 zu warten, um zu beenden, bevor SP1 endet.

SP2 wird als Teil von SP1 also habe ich so etwas wie:

 CREATE PROCEDURE SP1 AS BEGIN EXECUTE SP2 END 

T-SQL ist nicht asynchronous, also hast du wirklich keine andere Wahl, als zu warten, bis SP2 endet. Zum Glück, das ist, was Sie wollen.

 CREATE PROCEDURE SP1 AS EXEC SP2 PRINT 'Done' 

Hier ist ein Beispiel für eine unserer gespeicherten Prozeduren, die mehrere gespeicherte Prozeduren in ihr ausführt:

 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) ( 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) ) 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) LÖSCHEN VON 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 Gespeichertes Verfahren, das wir nach unserem Bedarf verwenden. Beispiel wie anders Gleicher Parameter mit verschiedenen Werten, die wir bei Abfragen verwenden müssen ..

 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 ( 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 ) 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 

Das ist, wie es funktioniert gespeicherte Prozeduren laufen in Ordnung, Sie brauchen nicht einfach so etwas wie

 exec dbo.sp1 exec dbo.sp2 

Ihr SP2 wird wahrscheinlich nicht laufen wegen eines Fehlers im frühen Code in SP1, so dass EXEC SP2 nicht erreicht wird.

Bitte posten Sie Ihren ganzen Code.

Hallo ich habe mein Problem gefunden, dass SP2 nicht aus SP1 ausgeführt wird, wenn SP1 ausgeführt wird.

Unten ist die Struktur von 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 ... 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