Hi I am calling one procedure from another procedure
now the called procedure throws some exception i want to catch
that exception and continue with some actions .Could you please help me out in this .My code is :
CREATE PROCEDURE PROC_DELETE_PROJECTS (IN P_PROJECT_NAME VARCHAR(35),OUT counter INT)
DYNAMIC RESULT SETS 1
LANGUAGE SQL

p1: BEGIN

DECLARE v_ProjectId INT DEFAULT 0;
DECLARE v_VariableId INT DEFAULT 0;
DECLARE v_DataConnectionId INT DEFAULT 0;
DECLARE v_Count INT DEFAULT 0;
DECLARE v_counter INT DEFAULT 0;
DECLARE var_count INT DEFAULT 0;
DECLARE del_count INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5);

DECLARE not_found
CONDITION for SQLSTATE '02000';

DECLARE ERROR_DELETE_VARIABLE
CONDITION FOR SQLSTATE '42501';


DECLARE at_end SMALLINT DEFAULT 0;



DECLARE c_ProjectVariables CURSOR FOR
select projvar_xref.variable_id
from projvar_xref
where projvar_xref.project_id= v_ProjectId
ORDER BY variable_id DESC;

DECLARE CONTINUE HANDLER for not_found
SET at_end = 1;

DECLARE CONTINUE HANDLER for ERROR_DELETE_VARIABLE
SET at_end = 2;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET at_end = 3;


SET counter=0;

SELECT PROJECT_MDI.ID
INTO v_ProjectId
FROM PROJECT_MDI
WHERE PROJECT_MDI.NAME = P_PROJECT_NAME ;

SELECT DATACONNECTION_ID
INTO v_DataConnectionId
FROM DATAPROJ_XREF
WHERE PROJECT_ID=v_ProjectId;

select projvar_xref.variable_id
INTO var_count
from projvar_xref
where projvar_xref.project_id= v_ProjectId
ORDER BY variable_id DESC;


DELETE FROM DATAPROJ_XREF WHERE DATAPROJ_XREF.PROJECT_ID = v_ProjectId;
DELETE FROM DATACONNECTION_MDI WHERE ID=v_DataConnectionId;
DELETE FROM CUPROJ_XREF WHERE CUPROJ_XREF.PROJECT_ID = v_ProjectId;
WHILE (del_count <> var_count) DO
SET v_counter=v_counter+1;
OPEN c_ProjectVariables;
fetch_loop:
LOOP

FETCH c_ProjectVariables INTO v_VariableId;
IF at_end <> 0 THEN LEAVE fetch_loop;
END IF;


SELECT COUNT(*) into v_Count
FROM PROJVAR_XREF
WHERE variable_id=v_VariableId
AND project_id <> v_ProjectId;

IF v_Count > 0 THEN

DELETE FROM PROJVAR_XREF
WHERE VARIABLE_ID=v_VariableId
AND PROJECT_ID=v_ProjectId;

UPDATE MODEL_MDI
SET ROOTVAR = NULL
WHERE ROOTVAR = v_VariableId;


ELSE



DELETE FROM PROJVAR_XREF
WHERE VARIABLE_ID=v_VariableId;

UPDATE MODEL_MDI
SET ROOTVAR = NULL
WHERE ROOTVAR = v_VariableId;

COMMIT;

CALL dbuser.PROC_DELETE_VARIABLE(v_VariableId);
IF at_end <> 3 THEN
SET del_count = del_count + 1;
END IF;

END IF;



END LOOP fetch_loop;

CLOSE c_ProjectVariables;
END WHILE;
SET counter = del_count ;
DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;
DELETE FROM PROJECT_MDI WHERE PROJECT_MDI.ID = v_ProjectId ;
COMMIT;

END p1





please help me its very urgent

CREATE PROCEDURE DB2DBA.proc_name
(IN in_var INTEGER,
:
OUT SQL_CODE INTEGER,
OUT SQL_STATE CHARACTER(5)
)
SPECIFIC DB2DBA.proc_name
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA

begin P1

declare SQLSTATE CHAR(5) DEFAULT '00000';
declare SQLCODE INT DEFAULT 0;
:
:

declare continue handler for sqlexception, sqlwarning, not found
begin
select sqlcode, sqlstate into v_sql_code, v_sql_state from sysibm.sysdummy1;
end;

:
:

set SQL_CODE = v_sql_code;
set SQL_STATE = v_sql_state;

end P1

put the condition handler in above for your called procedure. then pass back the sqlcode, sqlstate or both to trap the error code in the called procedure.

you may only want the sqlexception handler, depending on what type of warnings or not found condition could occur in the called procedure.

if you do not have the right condition handler for the error in the calling procedure, it will probably terminate on the error. not sure if this is what your question is or not.