Passing multiple record sets from one procedure to another

I am not having any luck getting this to work. I am using SQL 2000/2005
(we have both at the moment).
I am writing a stored procedure with the intent of creating a temp
table and inserting records into it. This procedure gets its records
from another sp. So SP 1 will call SP2. SP2, however, is used by other
applications directly, and it returns 3 recordsets. In my SP1, I only
want the results of the 1st recordset. Here is the flow:
SP 1:
- creates temp table
- Calls SP 2 and inserts the results of the first returned recordset
into the temp table
- selects/exports from temp table, blah blah...
I'm not sure how to get the results of the 1st recordset (only). Any
ideas?
TimModify the inner procedure, where you add one more optional parameter. When
you call this from your
outer proc, make sure that the inner proc only returns whatever it should re
turn in that particular
case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144339633.674358.270200@.e56g2000cwe.googlegroups.com...
>I am not having any luck getting this to work. I am using SQL 2000/2005
> (we have both at the moment).
> I am writing a stored procedure with the intent of creating a temp
> table and inserting records into it. This procedure gets its records
> from another sp. So SP 1 will call SP2. SP2, however, is used by other
> applications directly, and it returns 3 recordsets. In my SP1, I only
> want the results of the 1st recordset. Here is the flow:
> SP 1:
> - creates temp table
> - Calls SP 2 and inserts the results of the first returned recordset
> into the temp table
> - selects/exports from temp table, blah blah...
> I'm not sure how to get the results of the 1st recordset (only). Any
> ideas?
> Tim
>|||In this case, I could update both procedures, so that is what I did --
and that works great!
If there a way to handle this if I was not able to update the called
procedure?
Thanks,
Tim|||Without seeing the code, it is a little hard to comment, but this
sounds like you are still writing procedural code. Your description is
the way we would have used scartch tapes in the 1950's.
table [faking a scratch tape?] and inserting records [sic] into it.<<
You talk about "records" and not sets. You talk about a sequence of
procedure calls, just like a 3GL program. SQL is declarative; we do
things in a single statement whenever possible -- or without a
statement at all (i.e. VIEWs -- very handy and always up-to-date)!
But ignoring the lack of declarative programming in your narrative,
your procedural programming is not good.
Let's get back to coupling and cohesion, basic software engineering,
etc. You have what I call a "Britany Spears, Squid and Automobiles"
procedure. Each result should be created by one well-defined, coherent
procedure. Get out your old copy of Yourdon & DeMarco. This is far
more basic than SQL programming.|||I appreciate your response and commentary. Forgive me for not being
perfect, nor following your coding style. Rehashing the original
question so I can use proper terminology for you is a waste of time for
all of us. I work with what I have inherited just like everyone else.
And by the way, she spells her name Britney.
All the best,
Tim|||>>Forgive me for not being perfect, <<
Perfect is nice, but we can aim for competent, standard, etc.
Actually, it is ISO-11179 and not me. Then there is the whoel Yourdon,
DeMarco, et al stuff and DoD-2176 rules.
NO! If you use the wrong mental model, you will NEVER really get the
fundations of RDBMS. Words are concepts. We deal in a world of
abstractions -- the wrong words mean the wrong concepts. Do you
understand the problem with no having a "zero" and a "nothing", "null"
and "empty set" concepts in your math? HONKING BIG DIFFERENCE, UNH?
No, someone created the mess in the first place. God did not make Bade
Databases on the 8-th day, or even the 9-th. The real problem is that
peopel do nto fix them later (and kill the SOB who started the mess). .
I try to web-surf porno sites with girls who are more age-appropriate
to me; too bad most fo them are dead now). Except Ann-Margaret
(google it, kid).|||> If there a way to handle this if I was not able to update the called
> procedure?
If an outer proc calls an inner proc and that inner proc returns one result
set, you can do below in
the outer proc:
INSERT INTO ...
EXEC innerProc
But you can only catch the first result set from int inner proc this way. Th
e other result sets will
be returned to the client.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144689399.872944.186380@.v46g2000cwv.googlegroups.com...
> In this case, I could update both procedures, so that is what I did --
> and that works great!
> If there a way to handle this if I was not able to update the called
> procedure?
> Thanks,
> Tim
>|||I'm sorry--did you say something? All I heard was crap. You may sound
smart--even may be smart--but your tone and communication style really
makes you look like an idiot.|||ignore celko. he can't really help anyone.