Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    13

    Unanswered: Call SP multiple times inside a SP

    Hi, another new guy in db2 here, trying his luck in this forum. Ive tried to look everywhere but still having a hard time calling a stored procedure twice inside another stored procedure. I am using db2 Iseries / AS400 and doing the coding in inavigator.

    my code is something like this

    ===============================


    CREATE PROCEDURE TESTLIB/SPMAIN (
    OUT VAR1 VARCHAR(10) ,
    OUT VAR2 VARCHAR(10),)
    SPECIFIC TESTCC2/SP1
    DYNAMIC RESULT SETS 2
    LANGUAGE SQL



    P1: BEGIN

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    SET at_end2 = 1;

    DECLARE GLOBAL TEMPORARY TABLE SESSION/TEMPTABLE1
    (VAR1 VARCHAR(10),
    VAR2 VARCHAR(10),
    ) ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION/TEMPTABLE2
    (VAR1 VARCHAR(10),
    VAR2 VARCHAR(10),
    ) ;

    BEGIN
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE result2 RESULT_SET_LOCATOR VARYING;
    DECLARE cursor1 CURSOR WITH RETURN FOR SELECT * FROM SESSION/TEMPTABLE1;
    DECLARE cursor2 CURSOR WITH RETURN FOR SELECT * FROM SESSION/TEMPTABLE2;

    CALL TESTLIB/SP1 ('1','U');
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE TESTLIB/SP1;
    ALLOCATE rsCur1 CURSOR FOR RESULT SET result1;

    WHILE (at_end = 0) DO
    FETCH FROM rsCur1 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE1 values( VAR1 , VAR2 ) ;
    END WHILE;

    CALL TESTLIB/SP2 ('2','U');
    ASSOCIATE RESULT SET LOCATOR(result2) WITH PROCEDURE TESTLIB/SP2;
    ALLOCATE rsCur2 CURSOR FOR RESULT SET result2;

    WHILE (at_end2 = 0) DO
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE2 values( VAR1 , VAR2 ) ;
    END WHILE;

    OPEN cursor1;
    OPEN cursor2;

    END;

    END P1

    ===========================

    I am not getting any result for the result set no.2. I am not really sure whats the proper code in doing multiple sp calls, it would be easier if I will just use the query inside that sp but they want me to call the sp.

    Thanks, hope someones willing to give a hand and check my code or how to do it.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try by removing cursor1 and related code.
    It will help you to narrow the area of problem(s).


    Anyhow,
    questionable codes for me were...
    Note: Those questions may be not specific to DB2 or SQL.
    They must be similar in most other procedural languages.

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    (Repeat same statemens.)

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    SET at_end2 = 1; /* This must be a cause of your issue "I am not getting any result for the result set no.2." */
    (Scope of DECLARE HANDLER)
    (And suppose after execution of first WHILE loop.)

    OUT VAR1 VARCHAR(10) ,
    OUT VAR2 VARCHAR(10),)
    and
    FETCH FROM rsCur1 INTO VAR1, VAR2;
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    (Though it might work. But, using parameters as temporary variables may be not a good idea.)

    ...
    Last edited by tonkuma; 07-12-12 at 18:20. Reason: Remove "DYNAMIC RESULT SETS 2". Add Note.

  3. #3
    Join Date
    Jul 2012
    Posts
    13
    hi tonkuma, thanks for the reply.

    that was just a typo on my part im sorry. i corrected my sample sp. I tried to remove cursor1 related codes and it works and vice versa, but if i combine the codes to get 2 result, i can't get a result for result set no.2.

    ive tried to use an actual select query in for cursor2 and i can get some result.

    DECLARE cursor2 CURSOR WITH RETURN FOR SELECT * FROM SESSION/TEMPTABLE2;
    change to
    DECLARE cursor2 CURSOR WITH RETURN FOR SELECT 'sample_value' FROM TESTLIB/ACTUALTABLE;


    ===============================


    CREATE PROCEDURE TESTLIB/SPMAIN (
    OUT VAR1 VARCHAR(10) ,
    OUT VAR2 VARCHAR(10))
    SPECIFIC TESTLIB/SPMAIN
    DYNAMIC RESULT SETS 2
    LANGUAGE SQL



    P1: BEGIN

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE at_end2 SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    SET at_end2 = 1;

    DECLARE GLOBAL TEMPORARY TABLE SESSION/TEMPTABLE1
    (VAR1 VARCHAR(10),
    VAR2 VARCHAR(10)
    ) ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION/TEMPTABLE2
    (VAR1 VARCHAR(10),
    VAR2 VARCHAR(10)
    ) ;

    BEGIN
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE result2 RESULT_SET_LOCATOR VARYING;
    DECLARE cursor1 CURSOR WITH RETURN FOR SELECT * FROM SESSION/TEMPTABLE1;
    DECLARE cursor2 CURSOR WITH RETURN FOR SELECT * FROM SESSION/TEMPTABLE2;

    CALL TESTLIB/SP1 ('1','U');
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE TESTLIB/SP1;
    ALLOCATE rsCur1 CURSOR FOR RESULT SET result1;

    WHILE (at_end = 0) DO
    FETCH FROM rsCur1 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE1 values( VAR1 , VAR2 ) ;
    END WHILE;

    CALL TESTLIB/SP2 ('2','U');
    ASSOCIATE RESULT SET LOCATOR(result2) WITH PROCEDURE TESTLIB/SP2;
    ALLOCATE rsCur2 CURSOR FOR RESULT SET result2;

    WHILE (at_end2 = 0) DO
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE2 values( VAR1 , VAR2 ) ;
    END WHILE;

    OPEN cursor1;
    OPEN cursor2;

    END;

    END P1
    Last edited by marlu; 07-13-12 at 04:03. Reason: added addt'l info

  4. #4
    Join Date
    Jul 2012
    Posts
    13
    In sql I can do it the easier way calling the sp as it will only look like this

    --------
    insert into #temptable1
    exec SP1 ('1','U')

    insert into #temptable2
    exec SP2 ('2','U')

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    Please try by removing cursor1 and related code.
    It will help you to narrow the area of problem(s).


    Anyhow,
    questionable codes for me were...
    Note: Those questions may be not specific to DB2 or SQL.
    They must be similar in most other procedural languages.

    ...

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    SET at_end2 = 1; /* This must be a cause of your issue "I am not getting any result for the result set no.2." */
    (Scope of DECLARE HANDLER)
    (And suppose after execution of first WHILE loop.)

    OUT VAR1 VARCHAR(10) ,
    OUT VAR2 VARCHAR(10),)
    and
    FETCH FROM rsCur1 INTO VAR1, VAR2;
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    (Though it might work. But, using parameters as temporary variables may be not a good idea.)

    ...
    Please see my last post and consider what will the value of at_end2 be at each points in your code.

    For example:
    ...

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    SET at_end2 = 1;

    /* Point 1: What is the value of at_end2? */

    DECLARE GLOBAL TEMPORARY TABLE SESSION/TEMPTABLE1
    (VAR1 VARCHAR(10),
    VAR2 VARCHAR(10)
    ) ;

    ...

    WHILE (at_end = 0) DO
    FETCH FROM rsCur1 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE1 values( VAR1 , VAR2 ) ;
    END WHILE;

    /* Point 2: What is the value of at_end2? */

    CALL TESTLIB/SP2 ('2','U');
    ASSOCIATE RESULT SET LOCATOR(result2) WITH PROCEDURE TESTLIB/SP2;
    ALLOCATE rsCur2 CURSOR FOR RESULT SET result2;

    /* Point 3: What is the value of at_end2? */

    WHILE (at_end2 = 0) DO
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE2 values( VAR1 , VAR2 ) ;
    END WHILE;

    ...
    ...

  6. #6
    Join Date
    Jul 2012
    Posts
    13
    Bingo!! It's working!

    right after cursor2, i initialize at_end = 0 and used that in the WHILE and totally eliminated at_end2

    SET at_end = 0
    WHILE (at_end = 0) DO
    FETCH FROM rsCur2 INTO VAR1, VAR2;
    INSERT into SESSION/TEMPTABLE2 values( VAR1 , VAR2 ) ;
    END WHILE;

    Thank you so much tonkuma!!! have a nice day!

Tags for this Thread

Posting Permissions

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