Hi All !

I am reviewing SQL stored procedures written using stored procedure builder.
I noticed that they are all coded the following way

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE
SET l_sqlcode = 0

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab1;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE l_sqlcode = 0
logic...
FETCH
END WHILE;

Now my question is
After the initial SET of l_sqlcode (SET l_sqlcode = 0), Is the variable l_sqlcode always set automatically by the SQLCODE ? Or do you have to explicitly assign the value of SQLCODE (after a SQL statement) to l_sqlcode ?
Also, is there any reason to have a new variable l_sqlcode ? Can we not just check for SQLCODE after execution of every SQL statement ?
when would you declare a HANDLER for SQLEXCEPTION,SQLWARNING,NOT FOUND ?
and when would have a HANDLER just for SQLWARNING,NOT FOUND ?

lastly, I would appreciate if someone has a template SP (using SQL SP) that I can use as a reference.
thank you.

Anil

the handler is to easy : you don't have to check each time sqlcode. the handler will do. The problem is, the logic is the same for each condition.
If no handler defined, sqlcode should be checked and defined.
See appl progr guide how to check sqlcode in sql sp. there are some samples
Best Regards, Guy Przytula

There is an example of SQL Procedure Handlers at
http://www.db2click.com/scripts.htm

HTH

Sathyaram