By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

Nested SP:s and result sets

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.