Re: SQL PROC Consume Result Set of SQL PROC

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

Here is one of my examples .. ( snippet)

declare loc result_set_locator varying;


    -- get the result from the stored procedure
    call select_cutomers_for_shadow_letter();
    associate result set locator (loc) with procedure
select_cutomers_for_shadow_letter;
    allocate c1 cursor for result set loc;



On Thu, Aug 6, 2020 at 2:12 AM 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