Re: SQL PROC Consume Result Set of SQL PROC

Thanks you all for the assistance and advice. This is experimental code as I learn more about the SQL Procedural Language and figure out what I can do with it. For the record, here is what I have working:

CREATE OR REPLACE PROCEDURE lennons1.t()
LANGUAGE SQL
RESULT SETS 1
--SET OPTION OUTPUT=*PRINT
BEGIN
DECLARE x1 CURSOR with return to caller FOR
WITH
M AS
(SELECT lstnam, state
FROM qiws.qcustcdt
)
SELECT *
FROM M
order by lstnam
fetch first 5 rows only;
OPEN x1;
end;

T2, which calls T1.

CREATE OR REPLACE PROCEDURE lennons1.t2()
LANGUAGE SQL
BEGIN
declare SQLCODE Integer Default 0;
declare vlstnam char(30);
declare vstate char(2);
declare rs1 result_set_locator varying;
create table qtemp.tt (lstnam char(30), state char(2) );
call lennons1.t;
associate result set locator(rs1) with procedure lennons1.t;
allocate x2 cursor for result set rs1;
LblLoop: Loop fetch next from x2 into vlstnam, vstate;
if SQLCODE=100 or SQLCODE < 0 then leave LblLoop;
end if;
insert into qtemp.tt values(vlstnam, vstate);
end Loop;
close x2;
end;