Stored Procedure return multiple result sets

Question

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.

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?

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

EDIT:

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

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

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)

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

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.

Solution 2

At the end of the Stored Proc, put:

SELECT * FROM @myTable1
SELECT * FROM @myTable2

This will return 2 result sets.

Comments

[EJC]

Excellent I shall try this shortly :)

[JNK]

Good point! I didn't address this since he didn't mention the calling application.

[Jon Hanna]

@JNK, lol that's precisely the same reason I did mention it, in wondering why they'd had problems trying it I wondered if perhaps they were already doing it fine, but their tools are hiding it from them.

[EJC]

The results of this query will be emailed in HTML format from a C# application... This approach will still work? I haven't looked too deep into the application that calls the SP, I just know it can call an SP to get some results and then another program will email the results..

[JNK]

Bear in mind you will actually get THREE result sets, since it looks like you have one from the select at the end of your code in the OP.

[EJC]

This is not working... I get an error message, "must declare the scalar variable @myTable1" when doing the where clause "where @myTable1.field0 = @myTable2.field0" any thoughts?

[Jon Hanna]

That depends on how the application handles the results when it gets them. The most common behaviour with a .NET use of a query's results would be to just use the first resultset, though it could be calling NextResult() to get them all. If you can't change the application and the types of your two resultsets match, like they do in your question, you could combine them into a single resultset. I've added to my answer some code that does that.

[EJC]

Cool. Thanks for the suggestions and helpful code. I need to look into the code that calls the procedure to see what it does with the results.

[Branko Dimitrijevic]

Small correction: UNION ALL is not the default. SQL Server doesn't have UNION DISTINCT - just plain UNION is equivalent to UNION DISTINCT.

[Jon Hanna]

@BrankoDimitrijevic Correct. Rectified.