SQL TSQL：从另一个存储过程调用一个存储过程并读取结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3039200/
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
TSQL: Call a stored procedure from another stored procedure and read the result
提问by pistacchio
I have a stored procedure that, ending with a SELECT, returns a recordset. I can call it within anoher stored procedure like this:
我有一个以 SELECT 结尾的存储过程，它返回一个记录集。我可以在另一个存储过程中调用它，如下所示：
EXEC procedure @param
How to get the returning recordset? Thanks
如何获取返回的记录集？谢谢
回答by Ben Hoffstein
You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param.
您可以创建一个临时表，然后使用INSERT INTO #MyTable EXEC procedure @param.
There are some other techniques listed here.
回答by Kendrick
AFAIK, you can't. What you probably want to do is use a function for your first (or both) procedures. Functions can only return one thing, but they can return a table. Stored procedures can return multiple results, but not to other functions/stored procedures.
AFAIK，你不能。您可能想要做的是为您的第一个（或两个）过程使用一个函数。函数只能返回一件事，但它们可以返回一个表。存储过程可以返回多个结果，但不能返回到其他函数/存储过程。
e.g.:
例如：
CREATE FUNCTION [dbo].[fn_GetSubordinates] (
@sPersonID VARCHAR(10),
@nLevels INT
)
RETURNS @tblSubordinates TABLE
(
Person_Id VARCHAR(10),
Surname char(25),
Firstname char(25)
)
AS
BEGIN
...
回答by dretzlaff17
If you are using SQL Server 2008, I would recommend returning a Table-Valued Parameter.
如果您使用的是 SQL Server 2008，我建议您返回一个表值参数。
回答by user2355869
You can do this with an output variable in the stored proc. For example:
您可以使用存储过程中的输出变量来执行此操作。例如：
CREATE PROCEDURE sp_HelloWorld @MyReturnValue int OUT
AS
SELECT @MyReturnValue = 100
Return @MyReturnValue
To call this stored proc, do the following:
要调用此存储过程，请执行以下操作：
DECLARE @TestReturnVal int
EXEC sp_HelloWorld @TestReturnVal output
SELECT @TestReturnVal
回答by Dance-Henry
First, you CANNOT RETURNa recordset by stored procedure. By return, a stored procedure can only return integers.
首先，您不能通过存储过程返回记录集。通过返回，存储过程只能返回整数。
You mentioned SELECTstatement, which is a DQL and just for display purpose.
您提到了SELECT语句，这是一个 DQL，仅用于显示目的。
The way you can do to work around this issue is that you can assign the recordset to a global temporary table which can also be accessed within the outer stored procedure.
解决此问题的方法是将记录集分配给全局临时表，该表也可以在外部存储过程中访问。
