SQL 存储过程 OUTPUT 返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16126746/
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
SQL Stored Procedure OUTPUT Return Multiple Rows
提问by Kairan
I need to return a list of all reports in a report table that have not been processed yet. I am using stored procedures for this process.
我需要返回尚未处理的报告表中所有报告的列表。我正在为此过程使用存储过程。
Here is my stored procedure creation:
这是我的存储过程创建：
CREATE PROCEDURE spGetAllUntouchedReportID
@ReportID int OUTPUT
AS
BEGIN
SELECT @ReportID=Report.ReportID
FROM Report
WHERE Report.Status IS NULL
END
And this is how I am calling the stored procedure:
这就是我调用存储过程的方式：
DECLARE @ReportID int
EXECUTE spGetNextReportID
@ReportID OUTPUT
Print @ReportID
Essentially a C# app will be connecting and getting the result set but I am using the call to test it out. My problem is that the call to the stored procedure is only printing the LAST result it found.
本质上，一个 C# 应用程序将连接并获取结果集，但我正在使用调用来测试它。我的问题是对存储过程的调用只打印它找到的最后一个结果。
Here is an example set for the table of reports:
以下是报告表的示例集：
ReportID Status
1 NULL
2 Incomplete
3 NULL
A call to the procedure prints out:
对该过程的调用打印出：
3
Instead of:
代替：
1
3
How do I get my stored procedure to store the set that it returns from the stored procedure and call it to print each one out? This may come up in other stored procedures too where I need to do a Select * that has multiple columns and need to return the entire set
如何让我的存储过程存储它从存储过程返回的集合并调用它来打印每个？这也可能出现在其他存储过程中，我需要执行具有多个列的 Select * 并需要返回整个集合
回答by Taryn
The OUTPUT can hold one value in it. If you want to return a list of values, then you should alter the procedure to the following:
OUTPUT 可以在其中保存一个值。如果要返回值列表，则应将过程更改为以下内容：
CREATE PROCEDURE spGetAllUntouchedReportID
AS
BEGIN
SELECT Report.ReportID
FROM Report
WHERE Report.Status IS NULL
END
This will return all values that have a Statusof null.
这将返回所有具有 aStatus的值null。
回答by Gordon Linoff
My suggestion is to turn your stored procedure into a function:
我的建议是把你的存储过程变成一个函数：
CREATE function spGetAllUntouchedReportID
@ReportID int OUTPUT
returns table as
return (SELECT @ReportID=Report.ReportID
FROM Report
WHERE Report.Status IS NULL
)
The subject of returning and sharing values between stored procedures offers many solutions. Erland Sommerskog has an excellent blog articleon this subject.
在存储过程之间返回和共享值的主题提供了许多解决方案。Erland Sommerskog 有一篇关于这个主题的优秀博客文章。
