Re: SQL PROC Consume Result Set of SQL PROC

Note that using C1 for a cursor name is a bad idea. You should make sure
your cursor names are unique. Had the syntax you'd tried actually been
valid, you would have gotten a "cursor already open" in T2 since both use
C1 as a cursor name.

If you actually want to consume the cursor in t2...

I believe you'll need to take a look at the
ASSOCIATE LOCATORS
and
ALLOCATE CURSOR

statements.

If however you want to make the cursor from T1 available to whomever called
T2, then I think you'd want to have the cursor in T1 defined as WITH RETURN
TO CLIENT.

Personally, I've never tried consuming a stored procedure results set. If
I've got some business logic that builds a set of rows I want to reuse, I
usually just encapsulate the logic in a User Defined Table Function (UDTF).

Charles

On Wed, Aug 5, 2020 at 6:12 PM Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

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