Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: call sp from inside another sp

    All,

    I have two stored procs that both return resultsets and i would like to have a main stored proc and use this to call one or the other of my two stored procs.
    I would then like to return the resultset of the one I call back to the client.

    I have the following but it doesn't work, can't find much reference to it. Can anyone help?

    CREATE PROCEDURE MAINONE(IN MYVAR VARCHAR(50))
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN

    DECLARE C1 CURSOR WITH RETURN TO CALLER FOR

    IF MYVAR='1' THEN
    CALL PROC_ONE;
    ELSE
    CALL PROC_TWO;
    END IF;

    -- REALLY NOT SURE ABOUT NUMBER OF CURSORS TO OPEN OR TO RETURN ----TO WHAT? NOT SURE OF GENERAL STRUCTURE?

    BEGIN
    DECLARE C2 CURSOR WITH RETURN TO CLIENT FOR;
    C2 = C1;
    OPEN C2;
    END;

    END
    @

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's simple: in the nested procedure, you have to say WITH RETURN TO CLIENT and open the cursor there. The outer procedure doesn't care about cursors at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2008
    Posts
    19
    Do you mean as below? If so, this doesn't work. I get an error saying 'An unexpected token "CALL" was found following "RETURN TO CLIENT FOR"

    BEGIN

    IF MYVAR='1' THEN
    DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR
    CALL PROC_ONE;
    open C1;
    ELSE
    DECLARE C2 CURSOR WITH RETURN TO CLIENT FOR
    CALL PROC_TWO;
    open C2;
    END IF;



    END

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The 1st cursor is declared inside PROC_ONE and the 2nd cursor inside PROC_TWO. At those declarations, you have to say WITH RETURN TO CLIENT. You don't have to care about those cursors in your main procedure, i.e.
    Code:
    BEGIN
       IF MYVAR='1' THEN
          CALL PROC_ONE;
       ELSE
          CALL PROC_TWO;
       END IF;
    END
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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