Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: proceudre vs. function

    Hi,

    I created a proceudre that "returns" a value.

    CREATE PROCEDURE RSHIELD.P_GET_APPRCODE_FOR_ARN(IN v_ARN VARCHAR(23), OUT APPRCODE VARCHAR(6))
    LANGUAGE SQL
    READS SQL DATA

    BEGIN
    DECLARE SQLSTATE CHAR(5);
    --DECLARE APPRCODE1 VARCHAR(6);
    -- DECLARE v_COUNT INTEGER;
    -- for every error that may occur, return n.a. --
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, NOT FOUND SET APPRCODE = 'n.a.';

    -- get the number of selected rows, only if 1 return value
    SELECT APPRCODE INTO APPRCODE FROM RSHIELD.TRX_V_POSTING_ALL_PANID WHERE ARN = v_ARN with ur;

    END

    It's working.
    But if I try do implement the same structure as a function a get an error at line "SQLSTATE", at line "CONTINUE HANDLER" and at line "SELECT INTO..with ur".

    Why?

    I need the function rather than proc for calling it within a select-statement.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The condition handler is not allowed in a function. Change the select-into to set-select. Then check if the value is null and set it to your default value.

    Andy

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    The select can also return more than one rows, so I used the ROW_COUNT feature...OK
    But I also would like to use the "with ur" clause, it#s not working with normal select.

    Other idea: Can I call a procedure within a select??

    select value, function_a(arn) from table; ==> OK
    select value, procedure(arn,?) from table???

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you cannot call a stored procedure in a query. You can however wrap the stored procedure in a function.

    Andy

Posting Permissions

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