存储过程返回多个结果集

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.

这个问题解决了。谢谢你的家伙。

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)

2 个解决方案

#1

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不允许您从第二个resultset中读取()，直到您调用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 ALL or UNION DISTINCT (the default) where the latter will only send rows that aren't repeats.

您还可以选择UNION ALL或UNION DISTINCT(默认)，后者只发送不重复的行。

#2

At the end of the Stored Proc, put:

在存储Proc的末尾，放置:

SELECT * FROM @myTable1
SELECT * FROM @myTable2

This will return 2 result sets.

这将返回两个结果集。

#1