TSQL：从另一个存储过程调用存储过程并读取结果

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

如何获得返回的记录集？谢谢

5 个解决方案

#1

You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param.

您可以创建临时表，然后使用INSERT INTO #MyTable EXEC过程@param。

There are some other techniques listed here.

这里列出了一些其他技术。

#2

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
    ...

#3

If you are using SQL Server 2008, I would recommend returning a Table-Valued Parameter.

如果您使用的是SQL Server 2008，我建议您返回一个表值参数。

http://msdn.microsoft.com/en-us/library/bb510489.aspx

#4

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

#5

First, you CANNOT RETURN a recordset by stored procedure. By return, a stored procedure can only return integers.

首先，您不能通过存储过程返回记录集。通过返回，存储过程只能返回整数。

You mentioned SELECT statement, 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.

解决此问题的方法是，您可以将记录集分配给全局临时表，该表也可以在外部存储过程中访问。

#1