I am attempting to select the first 50 rows only of data and then update two columns and return the resultset of the rows that were updated to my calling application. The procedure below gets the rows and performs the update but does not return the resultset. Can someone explain to me what I am doing wrong that is causing the result set not to be returned?

CREATE PROCEDURE PROCESSVENDORPO
(in p_source varchar(50), in p_channel varchar(50), out p_rowcount integer)
DYNAMIC RESULT SETS 1
SPECIFIC PROCESSVENDORPO
LANGUAGE SQL MODIFIES SQL DATA
tr: begin
DECLARE v_sqlstatus INTEGER DEFAULT 0;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE v_vendor_po_id integer;
DECLARE v_orderid CHAR(30);

DECLARE c1 CURSOR WITH RETURN FOR
select vendor_po_id, orderid from vendor_po where source=p_source and channel=p_channel order by orderdate
FETCH FIRST 50 ROWS ONLY;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = -1;
OPEN c1;

loop_over_po:
LOOP
FETCH FROM c1
INTO v_vendor_po_id, v_orderid;
IF v_sqlstatus = -1 THEN
LEAVE loop_over_po;
END IF;
Update vendor_po set state='InProcess' where vendor_po_id = v_vendor_po_id;
SET v_counter = v_counter + 1;
END LOOP loop_over_po;
SET p_rowcount = v_counter;

end tr

If you want to return the resultset, you open the curosor and then return .. You are not allowed to fetch from it within the procedure.

Ok so can I update the entire result set with out using a FETCH and then return the result set? and if so how?

So I figured out my problem. I modified the input so I could pass in the number of rows I wanted to select and update instead of having that value hardcoded. So this procedure will will take in three parameters to use in my where clause then update the rows and return the result set of the updated rows and the rowcount of the rows that were updated.

CREATE PROCEDURE PROCESSVENDORPO
(in p_source varchar(50), in p_channel varchar(50),in p_batchsize integer, out p_rowcount integer)
DYNAMIC RESULT SETS 1
SPECIFIC HARLEDB.PROCESSVENDORPO
LANGUAGE SQL MODIFIES SQL DATA
tr: begin
DECLARE v_sqlstatus INTEGER DEFAULT 0;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE v_vendor_po_id integer;
DECLARE v_orderid CHAR(30);
DECLARE v_orderitemid CHAR(30);
DECLARE v_orderpayload VARCHAR(16000);
DECLARE v_orderdate TIMESTAMP;
DECLARE v_currenttime TIMESTAMP;
DECLARE v_receiveddate TIMESTAMP;
DECLARE v_destination CHAR(250);
DECLARE v_priority CHAR(30);
DECLARE v_sql VARCHAR(300);
DECLARE v_batchsize char(4);
DECLARE v_stmt statement;

DECLARE c1 cursor for v_stmt;


DECLARE c2 CURSOR WITH RETURN FOR
select vendor_po_id, orderid, orderitemid, orderpayload, orderdate, receiveddate, destination, priority
from harledb.vendor_po where state='InProcess' and LASTUPDATEDDATE=v_currenttime;

set v_currenttime=current timestamp;

set v_batchsize = RTRIM(CHAR(p_batchsize));

set v_sql='select vendor_po_id, orderid, orderitemid, orderpayload, orderdate, receiveddate, destination, priority'||
' from harledb.vendor_po where source='|| '''' || p_source||''''||
' and channel='|| ''''|| p_channel||''''||
' and state='||'''Waiting'''||
' and type='||'''New'''||
' order by orderdate FETCH FIRST '||
v_batchsize||
' ROWS ONLY';

prepare v_stmt from v_sql;

OPEN c1;


loop_over_po:
LOOP
FETCH FROM c1
INTO v_vendor_po_id, v_orderid, v_orderitemid, v_orderpayload,
v_orderdate, v_receiveddate, v_destination, v_priority;
IF SQLCODE=100 THEN
LEAVE loop_over_po;
END IF;
Update vendor_po set state='InProcess', LASTUPDATEDDATE=v_currenttime
where vendor_po_id = v_vendor_po_id;

SET v_counter = v_counter + 1;

END LOOP loop_over_po;
SET p_rowcount = v_counter;
CLOSE c1;


OPEN c2;

end tr

I think you could do that with a single statement, actually:

I wanted to be able to dynamically change the fetch amount.