returning result sets from one sproc to use in another sproc

Does anyone know if you can have the following situation and if so how:
One stored procedure would call another stored procedure. The second
stored procedure would return a query result (more than one row) to the calling
stored procedure. The first stored procedure (the initial calling stored
procedure) would then be able to use this 'table' or result set to query
against.
NOTE: We so not want to use global temp tables or cursors.

Thanks in advance!

2 ways I would do this ( in SQL 2K you could use a user defined function):

1. create a temp table in SP1 and have SP2 insert into it when EXECed and
then SP1 came use the data

2. create a temp table in SP1 and then call SP2 using insert #temp EXEC SP2

--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
--------------------
"amy" <amykr@firstlogic.com> wrote in message news:3cb6f955$1@10.1.10.29...
>
> Does anyone know if you can have the following situation and if so how:
> One stored procedure would call another stored procedure. The second
> stored procedure would return a query result (more than one row) to the
calling
> stored procedure. The first stored procedure (the initial calling stored
> procedure) would then be able to use this 'table' or result set to query
> against.
> NOTE: We so not want to use global temp tables or cursors.
>
> Thanks in advance!

Thanks!

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>p.s. there is a database.sqlserver.general forum ;-)
>"amy" <amykr@firstlogic.com> wrote in message news:3cb702a3@10.1.10.29...
>>
>> SQL Server 7.0
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >for which RDBMS ?
>> >
>> >"amy" <amykr@firstlogic.com> wrote in message
>news:3cb6f955$1@10.1.10.29...
>> >>
>> >> Does anyone know if you can have the following situation and if so
how:
>> >> One stored procedure would call another stored procedure. The
>second
>> >> stored procedure would return a query result (more than one row) to
the
>> >calling
>> >> stored procedure. The first stored procedure (the initial calling
>stored
>> >> procedure) would then be able to use this 'table' or result set to
>query
>> >> against.
>> >> NOTE: We so not want to use global temp tables or cursors.
>> >>
>> >> Thanks in advance!
>> >
>> >
>>
>
>