T-SQL Return Multiple Result Sets

Question

I'm hopeful that this isn't too awkward a question, but my searches so far haven't shed much light on things. My apologies if it's been asked before.

I need to write a stored proc to run a simple query along the lines of

SELECT foo 
  FROM sometable 
 WHERE somecolumn = @parameter

and then, for each foo execute another query, and return the results from each one as a separate result set.

I don't need to return the results of the initial query, just the results of executing the second query against each value of foo, and I don't want them combined into one result set, despite all having the same columns (it simplifies the presentation logic a lot if I can return multiple result sets).

This is on SQL-Server 2008 R2.

Thank you.

are you returning your results to another sql method or some external code? — Tanner, Mar 14 '14 at 15:53
As Mike said below, you could use a cursor to loop through. You can store the results of each individual query in a table variable, and then just select from each of those at the end of your procedure. The really interesting bit is, how will you know how many queries you're going to actually run? I think it would be vastly easier to return one resultset. Some tools will ignore anything after the first resultset anyway. — Andrew, Mar 14 '14 at 16:02

Aleksei Semidotskii · Accepted Answer · 2014-03-14T16:18:10.490

You should use CURSOR in you stored procedure like this:

DECLARE @foo VARCHAR(XXX)

DECLARE Curs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT 
    foo 
FROM sometable 
WHERE 
    somecolumn = @parameter

OPEN Curs

FETCH NEXT FROM Curs INTO @foo

WHILE @@FETCH_STATUS = 0
BEGIN

    -- here you should put actual query
    SELECT
    *
    FROM dbo.SomeTable
    WHERE
        foo = @foo

FETCH NEXT FROM Curs INTO @foo

END

CLOSE Curs
DEALLOCATE Curs

and then read cursor results using code like:

var sqlCommand = new SqlCommand("spYourStoredProcedure", connection)
sqlCommand.CommandType = CommandType.StoredProcedure;
var reader = sqlCommand.ExecuteReader();

do 
{
    while (reader.Read())
    {
        // DO SOMETHING
    }
} while(reader.NextResult());

score 1 · Answer 2 · answered Mar 14 '14 at 15:54

1

You could put the results of your SELECT statement into a cursor then loop through the cursor performing the second query against foo

This article explains cursors quite nicely

http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

answered Mar 14 '14 at 15:54

Mike

2,391
6
33
72

T-SQL Return Multiple Result Sets

2 Answers2