存储过程返回多个结果集 [英] Stored Procedure return multiple result sets

问题描述

我需要一个 SP 来返回多组结果.第二组结果将基于第一组结果的一列.

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.

所以:

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

如何用我的 SP 返回 @myTable1 和 @myTable2?这种语法完全正确吗?

How do return @myTable1 and @myTable2 with my SP? Is this syntax even right at all?

抱歉，我还是 SQL 的新手...

My apologies, I'm still a newbie at SQL...

因此，我在下面代码的最后一行收到错误消息:必须声明标量变量@myTable1""

So, I'm getting an error on the last line of the code below that says: "Must declare the scalar variable "@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

如果我突出显示并运行代码直到第二个 select * 它工作正常......当我突出显示其余部分时，它就像第一个变量不存在...

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

想通了那个问题.谢谢各位.

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)

推荐答案

您几乎只需选择两个结果集

You pretty much just select two result sets

SELECT * FROM @myTable1
SELECT * FROM @myTable2

然而，一些工具会隐藏一些结果(例如 pgAdmin 将只显示最后一个)，而一些工具有某种要求才能获得下一个结果集(例如 .NET 的 IDataReader 将不允许您 Read() 从第二个结果集开始，直到您调用 NextResult()).

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

在这种情况下，由于两个结果的类型匹配，另一种方法是将它们组合成一个结果集:

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

您还可以在 UNION ALL 或 UNION DISTINCT(默认值)之间进行选择，后者只会发送不重复的行.

You can also choose between UNION ALL or UNION DISTINCT (the default) where the latter will only send rows that aren't repeats.

这篇关于存储过程返回多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！