Return out parameter from stored procedure

asp_crazy_guy · #1 (**permalink**) 08-18-10, 03:31

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
    ;

Marcus_A · #2 (**permalink**) 08-18-10, 03:45

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

Internet Services

Web Development

Digital Marketing

Consumer Tech