I am using UDB V7.X on OS390.
I want to create nested SQL stored procs which return datasets.

This will explain the problem:

Stored proc1
{
this returns a Resultset
}

Stored proc2
{
this returns 2nd Resultset
}

Stored proc3
{
calls proc1 and proc2.
}

Can anyone please help me out with the syntax as to what can i do so that i get the 2 results as output when i run the stored proc3

Hi

Why don't you create one stored procedure with 2 cursors
and open them both inside the SP ?

Kristian

well thanks for that but that i have already implemented but i need to think about maintainablity as i need to implement the same thing for 11 result sets at a time. i hope you understand what i want to say.

In your child SP,
************************************************** ********
begin
declare <<CurName>> cursor with return to caller for
select ......
from ......
where .......
end
************************************************** ********
Caller SP,
************************************************** ********
begin
declare <<locator variable>> RESULT_SET_LOCATOR VARYING
call <<child sp name>>
associate result set locator <<locator variable>> with procedure <<child sp name>> ;
allocate <<Cur Name>> cursor for result set <<above locator variable name>> ;

end
************************************************** ********

Try & get back to me......

With Regards

Bala

i TRIED THIS TOO.
THIS IS THE SCRIPT FOR CHILD SP:


CREATE PROCEDURE DSNP.NEST1 ( )
RESULT SETS 1
LANGUAGE SQL
COLLID CLRIP000
WLM ENVIRONMENT BEVSP001@D2AD0
RUN OPTIONS ' '
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------

P1: BEGIN
-- Declare cursor
declare Cur cursor with return to caller for
select * from TBRIP20_RPF;
END P1


-------
BUT THIS DOES NOT COMPILE GIVING AN ERROR

DSNHPARS LINE 14 COL 32 INVALID KEYWORD "TO"; VALID SYMBOLS ARE: FOR

** WHAT SHOULD I DO????????????

************************************************** ********
CREATE PROCEDURE PARENTSP()
NOT DETERMINISTIC
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN ATOMIC
DECLARE my_var CHAR(10) DEFAULT '';
DECLARE my_rs RESULT_SET_LOCATOR VARYING;
CALL CHILDSP ;
ASSOCIATE RESULT SET LOCATOR (my_rs) WITH PROCEDURE CHILDSP;
ALLOCATE my_cur CURSOR FOR RESULT SET my_rs;
OPEN my_cur;
END
************************************************** ********
CREATE PROCEDURE CHILDSP()
NOT DETERMINISTIC
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN ATOMIC
DECLARE my_cur CURSOR WITH RETURN TO CALLER FOR
SELECT 'HELLO'
FROM SYSIBM.SYSDUMMY1;
OPEN my_cur;
END
;
************************************************** ********
Change the Cursor display as per your need....this is a sample only....

With Regards

Bala

hEY THIS SEEMS TO BE WORKING
BUT WHEN I OPEN THE CURSOR IN THE PARENT SP IT GIVES ME AN ERROR SAYING THIS CURSOR HAS BEEN DELARED USING AN ALLOCATE COMMAND.

SO HOW DO I RETURN THE RESULT SET TO THE PROGRAM FROM THE PARENT SP.