All,

I am using DB2, IBM Data Studio Release 2.2.1.0.
Putting my hands on to stored proc development, here is an issue I face.

Assume other parts of a Stored Procedure's body are present and absolutely compilable.

I am declaring a CURSOR
like below
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT * FROM TBL_1;
OPEN cursor1;
declare continue handler for not found set v_found_flg = 'N';

Above deploys absolutely fine.

But when I attempt to change above to a dynamic cursor, like below.

DECLARE e_msg varchar(400);
DECLARE cursor1 CURSOR WITH RETURN FOR s2;
SET e_msg = 'SELECT * FROM TBL_1';
PREPARE s2 FROM e_msg;
OPEN cursor1;
declare continue handler for not found set v_found_flg = 'N';

It is a simple change above, but I get an error on the line next to "OPEN cursor1", says expecting TOKEN. Note: I have not changed any other part of the existing stored procedure. All I did are :
1. added a varchar declaration
2. changed the declare cursor line
3. added a new line of code --> the set e_msg line
4. added another new line of code, the prepare line you see above

Have no clue what I've not done rightly.

could you publish the complete message you get..

Assume this is the very next line after my OPEN cursor1;


declare continue handler for not found set v_found_flg = 'N';

the error I get is :
ILLEGAL USE OF KEYWORD CONTINUE. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.11.69

As I mentioned, between the static and dynamic approaches, no other part of the program changed.

i have tried it
the declare continue handler has to be at the end of the declare section : see below

CREATE PROCEDURE DB2INST1.TT
(
)
LANGUAGE SQL
BEGIN
DECLARE v_found_flg char(1);
DECLARE e_msg varchar(400);
DECLARE s2 varchar(400);
DECLARE cursor1 CURSOR WITH RETURN FOR s2;
DECLARE CONTINUE HANDLER FOR NOT FOUND set v_found_flg = 'N';
SET e_msg = 'SELECT * FROM SYSIBM.SYSDUMMY1';
PREPARE s2 FROM e_msg;
OPEN cursor1;
END
DB20000I The SQL command completed successfully.

Great. It worked for me too! Thanks for your time.
Basics ! Basics !! Basics !!!

ok
have a look at doc
Compound SQL (Procedure)