Re: SQL PROC Consume Result Set of SQL PROC

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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