Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: calling stored procedure in a function

    Hello,

    I am using DB2 8.1 on windows.

    I created a stored procedure succesffully and when I try to use it in a function definition I get the error as below.

    Here is my script.

    connect to sample
    @
    set current schema = sample
    @
    grant execute on procedure sample.get_lang_proc to public
    @
    CREATE FUNCTION GET_LANG()
    RETURNS VARCHAR(3)
    SPECIFIC GET_LANG
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE V_lang varchar(3);
    call get_lang_proc(v_lang);
    return v_lang;
    END
    @

    Here is the error I get.

    Database Connection Information

    Database server = DB2/NT 8.1.0
    SQL authorization ID = MAKONAHA...
    Local database alias = SAMPLE


    DB20000I The SQL command completed successfully.

    DB20000I The SQL command completed successfully.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "call get_lang_proc(v_l" was found following "E
    V_lang varchar(3);". Expected tokens may include:
    "<SQL_condition_declaration". LINE NUMBER=1. SQLSTATE=42601


    I used "db2cmd db2.exd -td@ -fmydb2.db2" as the command to run my script from a dos box.

    Could someone tell me why I get the error with the SQLSTATE=42601 when my script runs.

    Thanks in advance.

    makonahalli

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    makonahalli,

    Call statements are not allowed in a function. Please refer to the SQL Reference Volumn 2 (Page 123) to see what is allowed.

    Andy

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    Originally posted by ARWinner
    makonahalli,

    Call statements are not allowed in a function. Please refer to the SQL Reference Volumn 2 (Page 123) to see what is allowed.

    Andy
    That's it. In sql function ,you can only use "return".

Posting Permissions

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