Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32

    Exclamation Unanswered: Problem calling a procedure in a function

    Someone knows how can i call a procedure in a function body, i've been tried like this:

    CREATE FUNCTION function1( p_dsc_ctx VARCHAR(7) ,
    p_dsc_colunm VARCHAR(18))
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    RETURNS VARCHAR(50)
    BEGIN ATOMIC

    DECLARE vReturn VARCHAR(50);

    call procedure1( p_dsc_ctx, p_dsc_colunm , vReturn );

    RETURN vReturn;
    END

    but this error occurs:

    [IBM][CLI Driver][DB2/LINUX] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=16. SQLSTATE=42613

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Calling SPs from UDF is not permitted for any version prior to 8.2.

    Andy

  3. #3
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    But in version 8.2 is permited and my db2 version is 8.2, you had already do something like this?

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    We are just now experimenting with 8.2 so we have not tried this yet. I looked at the grammar for CREATE FUNCTION because of the error message you posted. I think you need to move the RETURNS clause like this:

    CREATE FUNCTION function1( p_dsc_ctx VARCHAR(7) ,
    p_dsc_colunm VARCHAR(18))
    RETURNS VARCHAR(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    BEGIN ATOMIC

    DECLARE vReturn VARCHAR(50);

    call procedure1( p_dsc_ctx, p_dsc_colunm , vReturn );

    RETURN vReturn;
    END

    HTH
    Andy

  5. #5
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    I tried to do what you said but the error message continue, i'll try to find a solution for this case, if you find one please tell me.

    Thanks a lot.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What happens when you leave out the "MODIFIED SQL DATA" clause?

    Andy

  7. #7
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    I think i found the reason of my problem, i put the same name for the procedure, function and the global temporary table that the procedure reads i think db2 don't know in this case what are the statement he'll call, i change the name of then and db2 permits me to compile then.

    Thanks a lot.

  8. #8
    Join Date
    Dec 2004
    Posts
    54
    Can you post you example, what did you do to achieve for calling Sp from UDF?

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is hard to believe that db2 will be confused when using same name for a routine ,a function and a table .. Each one of them is a different type of object and used in a different fashion ... So, it has to be something else ...

    I have used UDFs and SPs with the same name before

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jan 2005
    Posts
    191
    My local installation of the documentation indicates that MODIFIES SQL DATA is "Valid only if RETURNS specifies a table (TABLE column-list)".

    James Campbell

Posting Permissions

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