467,118 Members | 988 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

Nested SP:s and result sets

Hi!

Guess this is a typical beginners question :-)

DB1 z/OS ver. 7.1

Two stored procedures each with a cursor.

There is no syntax like

DECLARE CURSOR .. WITH RETURN TO CALLER
or
DECLARE CURSOR .. WITH RETURN TO CLIENT

Is there a way to process the result set from SP2 in SP1 if SP1 calls
SP2?

I want to use the result from SP2 in SP1 as a intermediate result
before returning to the client (calling application)

Global temporary tables is one way to go I guess.

Cheers
Thomas
Nov 12 '05 #1
  • viewed: 1913
Share:
1 Reply
Hi,

In a Z/OS world all stored procedures are implicite return to caller.

what you need to do is declare your cursor (without any of the return
clauses) in your SP2 , then you need to open it also in SP2. However
don't do any fetches in your SP2, because these rows will be lost to
SP1.

meanwhile in SP1 you declare a result set locator, you connect your
resultset locator to your SP2, then you open a new cursor based on
this resultset and fetch.

the problem really begins if then you need to pass that resultset on
to the caller or SP1... then you need to fetch everything in to temp
table and declare a new cursor, which you open..

then in the caller of SP1 you declare result set locator , "link" that
to SP1, open a cursor... etc

I hope this helps

best regards

Kurt Struyf
senior consultant
Competence Partners Belgium

th************@spv.se (Thomasb) wrote in message news:<e8**************************@posting.google. com>...
Hi!

Guess this is a typical beginners question :-)

DB1 z/OS ver. 7.1

Two stored procedures each with a cursor.

There is no syntax like

DECLARE CURSOR .. WITH RETURN TO CALLER
or
DECLARE CURSOR .. WITH RETURN TO CLIENT

Is there a way to process the result set from SP2 in SP1 if SP1 calls
SP2?

I want to use the result from SP2 in SP1 as a intermediate result
before returning to the client (calling application)

Global temporary tables is one way to go I guess.

Cheers
Thomas

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Rhino | last post: by
9 posts views Thread by D. Shane Fowlkes | last post: by
8 posts views Thread by Sheldon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.