T-SQL返回多个结果集

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.

然后,为每个foo执行另一个查询,并将每个查询的结果作为单独的结果集返回。

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

我不需要返回初始查询的结果,只需要针对foo的每个值执行第二个查询的结果,并且我不希望它们组合成一个结果集,尽管它们都具有相同的列(它简化了如果我可以返回多个结果集,那么表示逻辑很多)。

This is on SQL-Server 2008 R2.

这是在SQL-Server 2008 R2上。

Thank you.

2 个解决方案

#1

You should use CURSOR in you stored procedure like this:

你应该在你的存储过程中使用CURSOR,如下所示:

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

#2

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

您可以将SELECT语句的结果放入游标,然后遍历游标,对foo执行第二个查询

This article explains cursors quite nicely

这篇文章很好地解释了游标

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

#1