SQL 存储过程返回多个结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3876146/
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
Stored Procedure return multiple result sets
提问by EJC
I need a SP to return multiple sets of results. The second set of results would be based on a column of the first set of results.
我需要一个 SP 来返回多组结果。第二组结果将基于第一组结果的列。
So:
所以：
declare @myTable1 table(field0 int,field1 varchar(255))
insert into @myTable1 select top 1 field0, field1 from table1
declare @myTable2 table(field0 int,field3 varchar(255))
insert into @myTable2
select field0, field3 from table2
where @myTable1.field0 = @myTable2.field0
How do return @myTable1 and @myTable2 with my SP? Is this syntax even right at all?
如何使用我的 SP 返回 @myTable1 和 @myTable2？这种语法甚至正确吗？
My apologies, I'm still a newbie at SQL...
抱歉，我还是 SQL 的新手...
EDIT:
编辑：
So, I'm getting an error on the last line of the code below that says: "Must declare the scalar variable "@myTable1""
因此，我在下面代码的最后一行收到错误消息：“必须声明标量变量“@myTable1””
declare @myTable1 table(field0 int,field1 dateTime)
insert into @myTable1
select top 1 field0, field1
from someTable1 m
where m.field4 > 6/29/2009
select * from @myTable1
select *
from someTable2 m2
where m2.field0 = @myTable1.field0
If I highlight and run the code up until the second select *it works fine...
when I highlight the rest it acts like the first variable doesn't exist...
如果我突出显示并运行代码直到第二个select *它工作正常......当我突出显示其余部分时，它就像第一个变量不存在......
EDIT2:Figured that problem out. Thanks guys.
EDIT2：想通了这个问题。谢谢你们。
declare @myTable1 table(field0 int,field1 dateTime)
insert into @myTable1
select top 1 field0, field1
from someTable1 m
where m.field4 > 6/29/2009
select * from @myTable1
select *
from someTable2 m2
where m2.field0 = (select field0 from @myTable1)
回答by Jon Hanna
You pretty much just select two result sets
您几乎只需选择两个结果集
SELECT * FROM @myTable1
SELECT * FROM @myTable2
However, some tools will hide some results (e.g. pgAdmin will only show the last) and some tools have some sort of requirement to get to the next result set (e.g. .NET's IDataReader's will not allow you to Read()from the second resultset until you call NextResult()).
然而，一些工具会隐藏一些结果（例如 pgAdmin 将只显示最后一个）并且一些工具有某种要求才能获得下一个结果集（例如，.NET 的 IDataReader 不允许您Read()从第二个结果集直到您调用NextResult()） .
Edit:
编辑：
An alternative in this case, since the types of the two results match, is to combine them into a single resultset:
在这种情况下，由于两个结果的类型匹配，另一种方法是将它们组合成一个结果集：
SELECT field0, field1 from @myTable1
UNION
SELECT field0, field3 from @myTable2
You can also choose between UNION ALLor UNION DISTINCT(the default) where the latter will only send rows that aren't repeats.
您还可以在UNION ALL或UNION DISTINCT（默认）之间进行选择，后者只会发送不重复的行。
回答by JNK
At the end of the Stored Proc, put:
在存储过程的末尾，输入：
SELECT * FROM @myTable1
SELECT * FROM @myTable2
This will return 2 result sets.
这将返回 2 个结果集。
