0

I have a procedure that returns a list when requested - test_1.

I want to create a procedure that sends each value from a table type_cd_list to the procedure test_1 and returns a list, but I get an error:

An existing result set Cursor is opened from stored procedure

Code:

--calling procedures
CALL Pb_tmd.test_1('280');

--create table
CREATE VOLATILE TABLE type_cd_list
(
    num_chn VARCHAR(10),
    info_system_type_cd VARCHAR(10)
) ON COMMIT PRESERVE ROWS;

INSERT INTO type_cd_list ('12','280');

--procedure
REPLACE PROCEDURE Pb_tmd.test_2()
    DYNAMIC RESULT SETS 1
BEGIN
    DECLARE hc1 VARCHAR(10);
    FOR
        RepordPointer AS c_sptable CURSOR FOR
        SELECT info_system_type_cd AS c_c1 FROM type_cd_list
        DO
            SET hc1 = RepordPointer.c_c1;
            CALL Pb_tmd.test_1(:hc1);
    END FOR;
ROLLBACK;
END;

Or is there another option to combine these 2 procedures? Below is the nested procedure code

REPLACE PROCEDURE Pb_tmd.test_1 (IN db_lst VARCHAR(100))
    DYNAMIC RESULT SETS 1
BEGIN
DECLARE NumberInst VARCHAR(100);
DECLARE SQL_TEXT VARCHAR(3000);
DECLARE cur1 CURSOR WITH RETURN ONLY FOR S1;
SET SQL_TEXT = 'SELECT * FROM Pb_'||db_lst||'_tmd.LOAD_LOG';
PREPARE S1 FROM SQL_TEXT;
OPEN cur1;
END;
mister bull
  • 1
  • 1
  • Not clear to me exactly what you are trying to do, but I suspect a Global Temporary Table would be the simplest approach. Test_1 could repeatedly insert data to the GTT and Test_2 could use the GTT for a ResultSet cursor. – Fred Aug 22 '22 at 13:36
  • 1
    Alternatively, Test_2 could call Test_1 using a cursor. Then Test_2 would fetch data from Test_1's resultset, accumulate the results (probably in a GTT instance), and close Test_1's resultset. After the loop completes, Test_2 can open its own resultset cursor to pass back the final result. – Fred Aug 22 '22 at 14:58
  • @Fred I am currently trying to do a similar thing: call a stored procedure inside another one and access the resultset. When I try to use a cursor to retrieve the resultset I get an error, that looks to me like cursors do only work together with sql statements. Is there a special syntax for using cursors with resultsets of stored procedures? – moons May 24 '23 at 09:27
  • Standard SQL. To access result set from a called procedure use ALLOCATE CURSOR FOR PROCEDURE instead of DECLARE CURSOR / OPEN CURSOR. – Fred May 25 '23 at 13:43

0 Answers0