使用嵌套存储过程导致调用存储过程Sql Server 2008 [英] Use nested stored procedure results in calling stored procedure Sql Server 2008

问题描述

是否可以在另一个存储过程中使用一个存储过程的结果?

Is it possible to use the results of one stored procedure in another stored procedure?

即

CREATE PROCEDURE [dbo].[Proc1]
        @ID INT,
        @mfgID INT,
        @DealerID INT

AS
BEGIN

    DECLARE @Proc1Result UserDefinedTableVariable

    EXEC @Proc1Result = Proc2
        @SomeID = @ID,
        @SomeID2 = @mfgID,
        @SomeID3 = @DealerID

    -- Now I want to use the table returned by the stored procedure here.
    SELECT [col1],[col2] FROM @Proc1Result

END

我尝试使用 INSERT INTO @Proc1Result EXEC Proc2(传递参数)，但是不能在嵌套语句中调用 INSERT EXEC.

I tried using INSERT INTO @Proc1Result EXEC Proc2 (with parameters passed), but INSERT EXEC cannot be called in a nested statement.

有什么办法可以做到这一点吗?环境是SQL Server 2008.

Is there any way of accomplishing this? The environment is SQL Server 2008.

推荐答案

您可以嵌套存储过程 最多 32 个级别.

You can nest stored procedures up to 32 levels.

我建议阅读有关 INSERT-EXEC 的这篇文章.这是一个片段:

I would recommend reading over this article regarding INSERT-EXEC. Here is a snippit:

如果 some_sp 试图调用 some_other_sp使用 INSERT-EXEC，你会得到一个错误信息.因此，您只能拥有一次激活一个 INSERT-EXEC.这个是 SQL Server 中的限制.

If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

这篇关于使用嵌套存储过程导致调用存储过程Sql Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！