我希望在一个存储过程中执行另一个存储过程,例如:
EXEC SP1
BEGIN
EXEC SP2
END
但我只想在SP2
运行结束后再让SP1
结束,所以我需要找到一种方法让SP1
等待SP2
完成后再结束。
SP2
是作为SP1
的一部分执行的,所以我的代码类似于:
CREATE PROCEDURE SP1
AS
BEGIN
EXECUTE SP2
END
我希望在一个存储过程中执行另一个存储过程,例如:
EXEC SP1
BEGIN
EXEC SP2
END
但我只想在SP2
运行结束后再让SP1
结束,所以我需要找到一种方法让SP1
等待SP2
完成后再结束。
SP2
是作为SP1
的一部分执行的,所以我的代码类似于:
CREATE PROCEDURE SP1
AS
BEGIN
EXECUTE SP2
END
T-SQL不是异步的,所以你别无选择,只能等到SP2结束。幸运的是,这正是你想要的。
CREATE PROCEDURE SP1 AS
EXEC SP2
PRINT 'Done'
这里是我们一个执行多个存储过程的存储过程示例:
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)
我们根据需要使用内联存储过程。例如,我们需要在查询中使用不同的相同参数和不同的值。
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
exec dbo.sp1
exec dbo.sp2