Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    6

    Unanswered: How to set the return value of a stored procedure to a variable inside another stored

    lets assume we have 2 stored procedure, DUMMY and INCREMENT

    CREATE PROCEDURE INCREMENT (INOUT I)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    SET I=I+1;
    END P1

    CREATE PROCEDURE DUMMY (INOUT I)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE I INTEGER;
    CALL INCREMENT (I);
    END P1

    I deployed the first procedure 'INCREMENT', but, when I try to deploy the second procedure 'DUMMY', it is throwing error that
    "No authorized routine named "INCREMENT" of type "PROCEDURE" having compatible arguments was found."

    What am I doing wrongly?? Please, let me know what to do??

    Is there another way to assign the output of a stored procedure to a variable inside a stored procedure?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The code you have shown cannot possibly generate the error you say it does.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No datatype for a parameter, like...
    CREATE PROCEDURE INCREMENT (INOUT I)
    ...
    It violates syntax, at least on DB2 10.1 for LUW.
    (n_i might pointed out the fact.)

    http://www-01.ibm.com/support/knowle...1.0%2F2-9-7-90
    DB2 for Linux UNIX and Windows 10.1.0 > Database reference > SQL > Statements > CREATE PROCEDURE (SQL)

    Note: I marked data-type.
    Syntax

    Code:
    >>-CREATE--+------------+--PROCEDURE--procedure-name------------>
               '-OR REPLACE-'                              
    
    >--+--------------------------------------------------------------------------------+-->
       '-(--+----------------------------------------------------------------------+--)-'   
            | .-,----------------------------------------------------------------. |        
            | V .-IN----.                                                        | |        
            '---+-------+--parameter-name--| data-type |--+--------------------+-+-'        
                +-OUT---+                                 '-| default-clause |-'            
                '-INOUT-'                                                                   
    
    >--| option-list |--| SQL-procedure-body |---------------------><
    
    data-type
    
    ...

    Another my issue was that two Is might be different variables.
    So you should use different names for each variables
    (or remove the DECLARE statement?).

    CREATE PROCEDURE DUMMY (INOUT I)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE I INTEGER;
    CALL INCREMENT (I);
    END P1

  4. #4
    Join Date
    Oct 2014
    Posts
    6
    n_i and tonkuma thanks for the reply.

    Sorry, n_i may I know why?

    Actually, Tonkuma sorry for the Confusion. I was just creating 2 dummy stored procedures to explain the issue. I missed the parameter type in both the stored procedure.

    Actually, The stored procedures may look like,

    CREATE PROCEDURE INCREMENT (INOUT I INTEGER)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    SET I=I+1;
    END P1

    CREATE PROCEDURE DUMMY (INOUT I INTEGER)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    --DECLARE I INTEGER;
    CALL INCREMENT (I);
    END P1

    I'm able to deploy the 'INCREMENT' Stored procedure. But, When I try to deploy the 2nd one (DUMMY), I'm getting the error as
    "No authorized routine named "INCREMENT" of type "PROCEDURE" having compatible arguments was found."

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Read the on-line documentation about the CURRENT PATH special register..


    The output of a procedure can be a result-set or output parameters or both.
    Your sample is unclear, because your definition includes the possibility of both, yet delivers only an output parameter (the code show will not deliver a result set).

    You will learn faster if you study properly the installation-directory SAMPLES directory contents which show stored procedures and other things..and if you make those samples work on your environment.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    A possibility I could imagine was schema name conflict, between
    "CREATE PROCEDURE INCREMENT (INOUT I INTEGER) ... "
    and
    "CALL INCREMENT (I);".

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Palrajjayaraj View Post
    Sorry, n_i may I know why?
    If you followed the proper forum etiquette in asking your question by providing a minimal, complete, verifiable example, you would immediately know why.
    Code:
    db2inst1:~/Desktop> cat /tmp/t.sql
    --#SET TERMINATOR @
    CREATE PROCEDURE INCREMENT (INOUT I)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    SET I=I+1;
    END P1
    @
    
    CREATE PROCEDURE DUMMY (INOUT I)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE I INTEGER;
    CALL INCREMENT (I);
    END P1
    @
    db2inst1:~/Desktop> db2 -tf /tmp/t.sql
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0204N  "I" is an undefined name.  LINE NUMBER=1.  SQLSTATE=42704
    
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0204N  "I" is an undefined name.  LINE NUMBER=1.  SQLSTATE=42704
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you may encounter such a "strange" situation in the following environment, for example:
    Code:
    SELECT * FROM TABLE(VALUES (SUBSTR(CURRENT PATH, 1, 50), SUBSTR(CURRENT SCHEMA, 1, 20))) T(PATH, SCHEMA)
    
    PATH                                               SCHEMA              
    -------------------------------------------------- --------------------
    "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","MARK_B"   DB2INST1
    Here you don't have the CURRENT SCHEMA in the CURRENT PATH list.
    You use unqualified names for your procedures: both for the CREATE and CALL statements.
    DB2 will create you procedures in the schema specified in the CURRENT SCHEMA registry variable. So, fully qualified name for INCREMENT routine will be DB2INST1.INCREMENT.
    But to qualify unqualified name in the CALL statement DB2 will search an appropriate routine in the list specified in the CURRENT PATH special registry. And since DB2INST1 is not in this list - DB2 fails to find it.
    Regards,
    Mark.

Posting Permissions

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