Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Exclamation Unanswered: Nested Stored Procs

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question Tried this ?

    Hi

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

    Kristian
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Jan 2006
    Posts
    4
    Quote Originally Posted by Tank
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    49

    Thumbs up Result set handlings ......in parent sp

    Quote Originally Posted by gauravjain911
    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
    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

  5. #5
    Join Date
    Jan 2006
    Posts
    4

    Cool

    Quote Originally Posted by bala_e
    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????????????

  6. #6
    Join Date
    Jan 2004
    Posts
    49

    Question Both Parent & Child SP

    ************************************************** ********
    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

  7. #7
    Join Date
    Jan 2006
    Posts
    4
    Quote Originally Posted by bala_e
    ************************************************** ********
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •