Consuming results sets in a calling SQL procedure

[p_le_sueur_1]

Dear All,

This is a query surrounding a problem I encountered
yesterday.

In SQL Server, it is possible to write a procedure that
has one or more select statements in it.

The results from these select statements will all be
individually returned to SQL Query Analyser where they
can be viewed in "grid" views. Also, these individual
results sets can be consumed by eg ADO.NET by stepping
through each results set in turn and processing the
respective results.

My question is, can you do the same in a SQL Server
procedure? ie:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

Create Procedure Proc2
AS
begin
exec Proc1
end

Can both/either of the results sets from Proc1 be
consumed by the calling procedure Proc2?

I can see that you could design the procedures up-
front to do almost anything without consuming the
result sets in this way, but if the procedures
returning the results sets are already built and
in use in other places (for instance in client code),
can they be re-used on server-side SQL procedures?
Thanks in anticipation!

Paul.

[sqlmatt]

In the example you have, the result to the caller of Proc 2 will see
the two results of the Proc1 procedure. As far as Proc2 "consuming" the
results of Proc1, there is no operation happening on the results in
your example.

Are you asking if you could return multiple tables from another proc
and do some manipulation on them, before you return those to the caller?

[p_le_sueur_1]

Thanks for the quick reply!

As far as Proc2 "consuming" the results of Proc1,
there is no operation happening on the results
in your example.
That is because I don't know how to represent it at
the moment - hopefully that's where you come in! ;-)
Are you asking if you could return multiple tables
from another proc and do some manipulation on them,
before you return those to the caller?

yes - whether I can return and manipulate one or more
data sets into a calling procedure, *without* changing my
original (called) procedures which take a form similar to:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

I hope this makes sense...

Thanks!

Paul.

[Dan Guzman]

You can use the NextResult method of a SqlDataReader object to process
multiple result sets returned from a single command. For example:

SqlDataReader myDataReader = myCommand.ExecuteReader();
while(true)
{
while(myDataReader.Read())
{
ProcessMyResults();
}
if(!myDataReader.NextResult()) break;
}
myDataReader.Close();
--
Hope this helps.

Dan Guzman
SQL Server MVP

<p_**********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks for the quick reply!

As far as Proc2 "consuming" the results of Proc1,
there is no operation happening on the results
in your example.

That is because I don't know how to represent it at
the moment - hopefully that's where you come in! ;-)

Are you asking if you could return multiple tables
from another proc and do some manipulation on them,
before you return those to the caller?

yes - whether I can return and manipulate one or more
data sets into a calling procedure, *without* changing my
original (called) procedures which take a form similar to:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

I hope this makes sense...

Thanks!

Paul.

[Ross Presser]

On 8 Dec 2004 04:17:30 -0800, p_**********@hotmail.com wrote:

Thanks for the quick reply!

As far as Proc2 "consuming" the results of Proc1,
there is no operation happening on the results
in your example.

That is because I don't know how to represent it at
the moment - hopefully that's where you come in! ;-)

Are you asking if you could return multiple tables
from another proc and do some manipulation on them,
before you return those to the caller?

yes - whether I can return and manipulate one or more
data sets into a calling procedure, *without* changing my
original (called) procedures which take a form similar to:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

I hope this makes sense...

Thanks!

Paul.

The INSERT INTO ... EXEC command can let a T-SQL batch (or procedure)
consume ONE resultset from another procedure, but not multiple resultsets.
Sorry.

Dan's method shows how to consume multiple resultsets from a .NET client,
but not from another stored procedure.

[Erland Sommarskog]

(p_**********@hotmail.com) writes:

My question is, can you do the same in a SQL Server
procedure? ie:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

Create Procedure Proc2
AS
begin
exec Proc1
end

Can both/either of the results sets from Proc1 be
consumed by the calling procedure Proc2?

When you call Proc2, the result sets go to the client. You can use
INSERT/EXEC to catch the data, but it only works if the result sets
are equally structures. (I think it works then, I am not sure.)

Anyway, I have an article on my web site, which discusses this in detail:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp