Hi,
in your caller procedure, you need to perform the following steps:
1. Define an RESULT_SET_LOCATOR
2. Call your procedure returning the result set
3. Associate the result set with the RESULT_SET_LOCATOR
4. Allocate a cursor for the result set
5. loop through the result set and FETCH the data into your host variables
6. Close the cursor
... something linke this:
Declare YourRSLOC01 Result_Set_Locator Varying;
Declare SQLCODE Integer Default 0;
-- Declare Variables for receiving the result set data
Call ResultSet_StoredProcedure(Parm1, ... ParmN);
Associtate Result Set Locator (YourRSLOC01);
Allocate YourCursor For Result Set YourRSLOC01;
LblLoop: Loop Fetch Next from YourCursor into YourHostVar1, ....
YourHostVarN;
If SQLCODE = 100 or SQLCODE < 0 Then Leave LblLoop;
End If;
-- Do whatever you want
End Loop;
Close YourCursor;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Sam_L
Sent: Donnerstag, 6. August 2020 02:12
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: SQL PROC Consume Result Set of SQL PROC
If I create proc "t" like this I can run it in Run SQL Scripts and get 3
records:
CREATE OR REPLACE PROCEDURE lennons1.t() LANGUAGE SQL
RESULT SETS 1
SET OPTION OUTPUT=*PRINT
BEGIN
DECLARE C1 CURSOR with return to caller FOR WITH
M AS
(SELECT lstnam, state
FROM qiws.qcustcdt
)
SELECT *
FROM M
fetch first 3 rows only;
OPEN C1;
end;
Then I tried to consume this in proc "t2", like this:
CREATE OR REPLACE PROCEDURE lennons1.t2() LANGUAGE SQL RESULT SETS 1 SET
OPTION OUTPUT=*PRINT BEGIN DECLARE C1 CURSOR FOR
values call(lennons1.t());
OPEN C1;
end;
If i "call t2" I get 'Message: [SQL0204] T in LENNONS1 type *N not found.'
Maybe I can't do this or I'm doing it all wrong. Any help would be
appreciated.
Thanks,
Sam
(And maybe the answer is somewhere on the IBM site, but the Knowledge Base
has been timing out all day.)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
