CODEWITHSUNDEEP
sqlloopsstored-proceduresdb2squirrel-sql
Sherri
Sherri

Reputation: 23

DB2 stored procedure in sql using squirrel. Unable to loop

I am unable to use %notfound as I get the following error. How can I get past this?? I'm unable to figure out a way to use cursors apart from this method. Please help with other methods to use a cursor loop without using %notfound.

The character "%" following "EXIT WHEN c_rqstid" is not valid.. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.63.123 SQL Code: -7, SQL State: 42601

I have set @ as the delimeter and the code is as follows

create PROCEDURE TEST111()
AS: 
begin 
DECLARE c_id integer; 
DECLARE c_isactive integer; 
DECLARE c_status integer; 
CURSOR c_rqstid is SELECT REQUESTID,REQUESTSTATUS,ISACTIVE FROM SAMPLE.REQUEST;
 OPEN c_rqstid;
  FOR LOOP FETCH c_rqstid into c_id,c_status,c_isactive ;
  ----will code this later
  EXIT WHEN c_rqstid%NOTFOUND;
  END LOOP;
 CLOSE c_rqstid;
end
@

Thanks for any help in advance...

Upvotes: 2

Views: 4007

Answers (1)

Charles
Charles

Reputation: 23783

You'll need to check the SQLCODE or SQLSTATE variables:

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0009028.html

 DECLARE SQLCODE INTEGER DEFAULT 0;
 DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

 WHILE(SQLSTATE = '00000') DO
    SET p_sum = p_sum + p_sal;
    FETCH FROM c INTO p_sal; 
 END WHILE;

Upvotes: 2

Related Questions