Hi,

I have written 2 stored procedure on db2 7.2

a>TEST.SELECT-------------------Callee Procedure

b>TEST.INSERT ------------------Caller Procedure

I am testing from SPB. TEST.SELECT function properly when run. however when called from inside TEST.INSERT it returns 0;

Can any body suggest me how to retrieve the result set returned by TEST.SELECT.basically not quite sure how to call it properly from inside of TEST.INSERT


The source code is as follows.

a>
************************************************** ******

CREATE PROCEDURE TEST.SELECT()
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- This SQL Stored Procedure returns the resultset for all the rows in table TEST.TEST_STAGING_1
------------------------------------------------------------------------

P1: BEGIN

DECLARE RESULT CURSOR WITH RETURN TO CALLER
FOR SELECT IMCNUMBER,STATUS,LOGONID,LOGONPASSWORD,NAME,ADDRES SLINE1,ADDRESSLINE2,CITY
FROM TEST.TEST_STAGING_1;
OPEN RESULT;

END P1


************************************************** ******


b>

CREATE PROCEDURE TEST.INSERT (IN parent_table VARCHAR(80), IN child_table VARCHAR(80) )
LANGUAGE SQL

------------------------------------------------------------------------------------------------------
-- This SQL Stored Procedure calls procedure TEST.SELECT AND INSERT THE DATA IN THE RESPECTIVE TABLES
-------------------------------------------------------------------------------------------------------

P1: BEGIN



DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
DECLARE I,J INTEGER;

DECLARE v_imc BIGINT;
DECLARE v_parent_pk BIGINT;
DECLARE v_child_pk BIGINT;
DECLARE v_status CHAR(2) DEFAULT 'S';
DECLARE v_logonid VARCHAR(25) ;
DECLARE v_logonpassword CHAR(10);
DECLARE v_name varchar(80);
DECLARE v_addressline1 varchar(255);
DECLARE v_addressline2 varchar(255);
DECLARE v_city char(10);
DECLARE v_iterate CHAR(5);

CALL TEST.SELECT;

ASSOCIATE RESULT SET LOCATOR(LOC1) WITH PROCEDURE TEST.SELECT;
-- ALLOCATE C1 CURSOR FOR RESULT SET LOC1;

-- SET I =(SELECT COUNT (*) FROM TEST.TEST_STAGING_1);
-- SET J=0;

-- WHILE(J<I) DO

--Get tables primary key
-- SELECT PK_VALUE INTO v_parent_pk FROM TEST.KEYS WHERE TABLE_NAME=parent_table;
-- SELECT PK_VALUE INTO v_child_pk FROM TEST.KEYS WHERE TABLE_NAME=child_table;

-- FETCH FROM C1 INTO v_imc,v_status,v_logonid,v_logonpassword,v_name,v_ addressline1,v_addressline1,v_city;

-- INSERT INTO TEST.TEST_PARENT_1(USERS_ID,STATUS,LOGONID,LOGONPA SSWORD)
-- VALUES (v_parent_pk,v_status,v_logonid,v_logonpassword);

-- INSERT INTO TEST.TEST_CHILD_1(USERS_ID,NAME,ADDRESSLINE1,ADDRE SSLINE2,CITY)
-- VALUES (v_child_pk, v_name,v_addressline1,v_addressline2,v_city);

-- UPDATE TEST.KEYS SET PK_VALUE = v_parent_pk+1 WHERE TABLE_NAME=parent_table;

-- UPDATE TEST.KEYS SET PK_VALUE = v_child_pk+1 WHERE TABLE_NAME=child_table;

-- SET J=J+1;

-- END WHILE;

END P1

************************************************** ******


Thanking in advance

Regards

Ramdas Nayak