在存储过程中执行存储过程。

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.

但是我只希望SP1在SP2完成后完成，所以我需要找到一个方法让SP1在SP1结束前等待SP2完成。

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

SP2作为SP1的一部分被执行，所以我有如下内容:

CREATE PROCEDURE SP1
AS
BEGIN

EXECUTE SP2

END

6 个解决方案

#1

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

T-SQL不是异步的，所以您别无选择，只能等到SP2结束。幸运的是，这正是你想要的。

CREATE PROCEDURE SP1 AS
   EXEC SP2
   PRINT 'Done'

#2

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)

#3

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

#4

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

这就是它如何运行存储过程的顺序，你不需要开始就像这样。

exec dbo.sp1
exec dbo.sp2

#5

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

您的SP2可能没有运行，因为在SP1的早期代码中出现了一些失败，所以无法到达EXEC SP2。

Please post your entire code.

请张贴您的全部代码。

#6

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

Hi，我发现我的问题是SP2在SP1执行时不会从SP1中执行。

Below is the structure of SP1:

下面是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

#1