SQL 使用嵌套存储过程导致调用存储过程Sql Server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4880627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use nested stored procedure results in calling stored procedure Sql Server 2008
提问by JD Stuart
Is it possible to use the results of one stored procedure in another stored procedure?
是否可以在另一个存储过程中使用一个存储过程的结果？
I.e.
IE
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
I tried using INSERT INTO @Proc1Result EXEC Proc2 (with parameters passed), but INSERT EXECcannot be called in a nested statement.
我尝试使用INSERT INTO @Proc1Result EXEC Proc2 (with parameters passed)，但INSERT EXEC不能在嵌套语句中调用。
Is there any way of accomplishing this? The environment is SQL Server 2008.
有没有办法做到这一点？环境是 SQL Server 2008。
回答by Abe Miessler
You can nest stored procedures up to 32 levels.
您最多可以嵌套32 个级别的存储过程。
I would recommend reading over this articleregarding INSERT-EXEC. Here is a snippit:
我建议阅读这篇关于 INSERT-EXEC 的文章。这是一个片段：
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.
如果 some_sp 尝试使用 INSERT-EXEC 调用 some_other_sp，您将收到一条错误消息。因此，您一次只能激活一个 INSERT-EXEC。这是 SQL Server 中的限制。
回答by Remus Rusanu
You're right, INSERT ... EXECcannot nest. From How to Share Data Between Stored Procedures.
你说得对，INSERT ... EXEC不能嵌套。从如何在存储过程之间共享数据。
It can't nest. If
some_sptries to callsome_other_spwith 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.
它不能筑巢。如果
some_sp尝试some_other_sp使用 INSERT-EXEC调用，您将收到一条错误消息。因此，您一次只能激活一个 INSERT-EXEC。这是 SQL Server 中的限制。
You need to find some other way. Erland's article linked goes trough pretty much all the options you have and discusses them at great detail.
你需要找到其他方法。Erland 的文章链接几乎涵盖了您拥有的所有选项，并详细讨论了它们。
回答by Mark SQLDev
One syntax for getting results into one sp from another is:
将结果从另一个 sp 导入一个 sp 的一种语法是：
INSERT INTO [myTable]
EXEC Proc1 [param1], [param2], [param3], etc.
But you have to create the table first that it inserts into and the field names and types have to exactly match.
但是您必须首先创建它插入的表，并且字段名称和类型必须完全匹配。
