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

Modify 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 return in that particular
case.

Show quoteHide quote

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

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. The other result sets will
be returned to the client.

Show quoteHide quote

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

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).

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.

More on querying remote server
many small queries vs one large query
Plz help me! I want to use SQL Server2000 DTS Import/Export Wizard functionality from C# application
Default Values for a Column
case expression in where clause and null's