All Forums General
larun1616 29 posts Joined 09/14
18 Sep 2014
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.

You must sign in to leave a comment.