how to read a resultset from a procedure calling a procedure

Hi all,
I'm trying to user proc1 to call proc2.
proc2 is a search proc which returns a resultset, but how can i have proc1
read that resultset without having that resultset being sent to the client?
i need to read that resultset in proc1 and then make another resultset out
of that one that will return to the client.
i was thinking temporary tables but they dont go across procedures and
##global tables dont do the trick cause they arent unique
any ideas?
thanksPedro,
Check out Erland's article at:
http://www.sommarskog.se/share_data.html
HTH
Jerry
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||You could replace Proc2 with a VIEW ?
Look up CREATE VIEW in BOL - this will give you all the information you
should need regarding Views.
HTH
Barry|||CREATE TABLE #<temptablename>
(
<colspec>
)
INSERT #<temptablename>
EXEC <storedprocedurename>|||The scope of temporary tables does extend to called stored procedures. If
you create a temp table within one stored procedure, it is visible in any
stored procedure called by that stored procedure, provided the CREATE TABLE
occurs before the EXEC. You can also create a temp table and issue
INSERT...EXEC to populate it with the result set of another stored
procedure.
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||Have you thought of using a table variable returned to proc1 from Proc2
Lookup OUTPUT in BOL
GW
"Pedro C Valenzuela" wrote:

> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the client
?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>
>|||as far as i can recall, you cant use table variables for output parameters
and i havent found it in BOL either, am i wrong?
"GWilliy" <GWilliy@.discussions.microsoft.com> wrote in message
news:DB2AC26B-FCB9-45D9-ABEE-57CF97079878@.microsoft.com...
> Have you thought of using a table variable returned to proc1 from Proc2
> Lookup OUTPUT in BOL
> GW
> "Pedro C Valenzuela" wrote:
>|||On Tue, 27 Sep 2005 18:53:13 -0700, Pedro C Valenzuela wrote:

>as far as i can recall, you cant use table variables for output parameters
>and i havent found it in BOL either, am i wrong?
Hi Pedro,
No, you're right. Table variables can't be passed as input parameter to
stored procedures, nor returned as output parameter from stored
procedures. That's what table-valued user-defined functions are for.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)