CAPTURING ERRORS IN STORED PROCEDURE

General
Enthusiast

CAPTURING ERRORS IN STORED PROCEDURE

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 13031 StartFragment: 314 EndFragment: 12999 StartSelection: 314 EndSelection: 314

REPLACE PROCEDURE DBNAME.PROC(
IN PROD_NAME VARCHAR(50)
)
BEGIN
DECLARE SState INTEGER;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

CALL DBC.SYSEXECSQL
(
'
REPLACE VIEW DB_NAME.TEST AS
(
SELECT A_C_T FROM DB_NAME.Tab1 WHERE PROD_Name LIKE ''%'
||PROD_NAME||'%''

);

'
);

CALL DBC.SYSEXECSQL
(
' INSERT INTO DB_NAMe.Tab2
SELECT CURRENT_TIME
FROM DBC.ERRORMSGS;'
);

SELECT :SQLSTATE INTO SState;
IF (SState <>0)
THEN BEGIN
INSERT INTO DB_NAME.ErrorLogTable
SELECT CURRENT_DATE,CURRENT_TIME, 'Proc_Name',:SQLSTATE,:SQLCODE,ERRORTEXT
FROM DBC.ERRORMSGS
WHERE errorCode=:SQLCODE;
END;

ELSE
BEGIN
INSERT INTO DB_NAME.ErrorLogTable
SELECT CURRENT_DATE,CURRENT_TIME, 'Proc_Name',:SQLSTATE,:SQLCODE,'Success'
FROM DBC.ERRORMSGS;
END;
END IF;

END;

CALL DB_NAME.PROC_RX_TEST(''||'BOOST'||'');

Here,

Can we log error into DB_NAME.ErrorLogTable and halt query execution without handling that exeception when SQL query fails?

Also, can i log success or error into a table after every SQL query executed in DBC.SYSEXECSQL instead of logging once for every procedure as shown in the above procedure?

Please help me.