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