Consuming results sets in a calling SQL procedure

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.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?|||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.|||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_le_sueur_1@.hotmail.com> wrote in message
news:1102508250.805756.307860@.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.|||On 8 Dec 2004 04:17:30 -0800, p_le_sueur_1@.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.|||(p_le_sueur_1@.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, esquel@.sommarskog.se

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

consuming result sets from dynamic queries within a cursor

Hi SQLers
I have the flowing scenario. I have a cursor that amongst other things
returns me the name of a stored procedure that needs to run and a parameter
to pass to it. Depending on whether the stored proc returns a result I then
decide what to do with the other data I get from the cursor. But I want to
suppress the result sets coming from the inner stored proc calls.
I basically want something like this in my cursor loop
insert into #tmp exec ( @.StoredProcedureName + ' ' + @.messageID )
if exists (select 1 from #tmp)
-- do something with cursor data
else
-- do something else with cursor data
-- where @.StoredProcedureName and @.messageID are data from the cursor
but I get an error saying invalid object #tmp
I cannot declare #tmp as a table variable as unfortunately the stored
proceedures called are different for different iterations of the cursor, tha
t
is @.StoredProcedureName changes and the result set from the different
procedures is different. I cannot make them the same as each of these proc
does a different job and they are called by different callers and so cannot
be changed.
and I dont want to say
exec ( @.StoredProcedureName + ' ' + @.messageID )
if (@.@.ROWCOUNT > 0)
-- do something
else
-- do something else
as the result set of the exec is then returned to the caller.
If anyone has any ideas I would be very greatful. I know some ways to solve
this in C# .NET but I was just wondering if there is a SQL solution. I expec
t
there is not but thought I would ask those that are likely to know.
Many thanks
DerekI don't understand why you would want to execute a stored proc dynamically
based on a cursor. TSQL does have IF statements for flow control but what
you are describing is a very procedural solution. Are you sure there isn't a
set-based SQL alternative to all this?
Read this article on how to do dynamic SQL (and why NOT to do it):
http://www.sommarskog.se/dynamic_sql.html
This one explains how you can process result sets from an SP:
http://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi David,
Thanks for your time.
Basically my cursor is finding all entries in a MessageReceipients table
that have NULL as their SentDateTime and once armed with the MessageID and
@.MessageType for these intended recipients, I need to call the appropriate
stored procedures (the actual stored proc will depend on the MessageType) to
see if these receipients can still have messages sent to them.
For example it is possible that a message receipient is in the
MessageRecipient table and that the mail system originally failed to send th
e
message but by the time we come to do this check (to resend the message) the
intended recipient my no longer want or be able to receive messages. This
fact can only be determined by calling the @.StoredProcedureName (in my
original code sample) and the reason this is dynamic is that the procedure
that needs to be checked to determin if a message can still be sent is
different for different message types.
I know I can solve this problem in my C# code and perhaps this is the place
to do it. For example I could just return all receipients that have got
potentially outstanding messages. And in the C# check the @.MessageType and
@.MessageID and then call the @.StoredProcedureName with @.MessageID to deterim
whether it is really appropriate to send the message to the receipient.
I was just hoping to not have to do this step in the C# application if I
could avoid it.
I also realise that re writing the various @.StoredProcedureName SPs would
make life easier but unfortunately they are used by other parts of the app
and so I cannot change them. So I would have to esentially duplicate there
functionality with the exception of how they return their results and that
would not be good from a maintenance perspective.
Having said that for now all I am doing in the C# application is looking at
the last result set returned from my SP as this is the result set that I
want. But this is only ok as the results sets are quite small and so I do no
t
need to worry to much about returning unwanted data to the C# application.
Hope that make things a little clearer
Many thanks
Derek
"David Portas" wrote:

> I don't understand why you would want to execute a stored proc dynamically
> based on a cursor. TSQL does have IF statements for flow control but what
> you are describing is a very procedural solution. Are you sure there isn't
a
> set-based SQL alternative to all this?
> Read this article on how to do dynamic SQL (and why NOT to do it):
> http://www.sommarskog.se/dynamic_sql.html
> This one explains how you can process result sets from an SP:
> http://www.sommarskog.se/share_data.html
> --
> David Portas
> SQL Server MVP
> --
>
>

Constants to Sets

Hi,

I guess that's an easy one... I'm quite sure that my brain is gone for weekend... And I'm still getting used to MDX...

I'm using the min function to find out the minimum of two measures, so basically:

min({[Measures].[MeasureA],[Measures].[MeasureB]})

This works perfectly. But what I need is to have something like that:

min({[Measures].[MeasureA],[Measures].[MeasureB],0})

So that when A and B are both greater than 0 the result is 0. OK, I can do that with iif but that's not nice... There must be a way to add a constant to a set... I even need something like that:

min ({sum(...),sum(...),0})

I guess it's the same problem and the same solution...

Any help is very appreciated...

is this what you mean?

func(1,2) -> 0

func(-1,2) -> -1

func(-1,-2) -> -2

its not much prettier than iif -

Min(Min(0, a), Min(0, b))

|||

Well, you understood what I'm looking for... However I'm quite sure that this doesn't work...

The MDX min function expects a set. What you provide is value (as far as I know normally called an expression), that doesn't seam to work at all...

|||

sorry. . mdx is not my forte. . .

from the help -

min(Set-Expression, [Numeric-Expression]) evaluates Numeric over Set

can't you make a set from two constants?

Evalute Min Zero over a, And Min Zero over b,

'set' results and min -

Min( { Min({a},0), Min({b},0) } )

|||

ught. . Im not thinking. . .

isnt it

Min({a,b},0)

|||That was my first try... Doesn't work... The result is always 0, no idea why...|||

Hi Thomas,

How about using a calculated measure for the constant lower limit, like in this Adventure Works example:

>>

With Member [Measures].[LowLimitOrder] as 2500

Member [Measures].[MinOrderCount] as

Min({[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[LowLimitOrder]})

select {[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[MinOrderCount]} on 0,

[Product].[Product Categories].[Category].Members on 1

from [Adventure Works]

Internet Order Count Reseller Order Count MinOrderCount
Accessories 18,208 1,315 1315
Bikes 15,205 3,153 2500
Clothing 7,461 2,410 2410
Components (null) 2,646 2500

>>

|||

Deepak,

once again... THANKS for your superb support! Works like a charme...