Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Red face Unanswered: Return out parameter from stored procedure

    Hello guys I want to return the updated record count from my stored procedure. Here is what I have managed so far. I don't understand how to return it through a cursor. Is it necessary at all ?

    Code:
    CREATE PROCEDURE PAKRETST.FTUMODINST (IN GENFORTREATYSRNO INTEGER,IN GENFTMDPDUENO INTEGER, OUT NUMREC SMALLINT)
    RESULT SETS 0 MODIFIES SQL DATA LANGUAGE SQL
    P1:BEGIN
        DELCARE strCmd VARCHAR(500);
        DECLARE x CURSOR WITH RETURN TO CALLER FOR SL;
        SET strCmd='UPDATE PAKRETST.UWFTMDPDUEDATES a where a.GENFORTREATYSRNO='||GENFORTREATYSRNO||'AND a.GENFTMDPDUENO='||GENFTMDPDUENO;
        PREPARE SL FROM strCmd;
        SET NUMREC= -- Stuck here --
        RETURN;
        END
        ;

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can return the row count as an out parmeter. Here is a SP example:

    create procedure update_salary
    (in original_salary integer, in new_salary integer, out rows_updated integer)
    language sql

    begin

    update employee set salary = new_salary where salary = original_salary;
    get diagnostics rows_updated = ROW_COUNT;

    end
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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