Archives of the TeradataForum
Message Posted: Fri, 22 Oct 2004 @ 22:34:16 GMT
I am writing teradata stored procedure (V2R5) and I would like to raise exception based upon a particular SQL statement, so I can customize error message logging.
I am using two types for error handlers; DECLARE EXIT HANDLER FOR SQLEXCEPTION or DECLARE CONTINUE HANDLER FOR SQLEXCEPTION but both of them are raised by teradata system internally i.e. Anytime an error occurs then teradata will jump to the section of HANDLER. It will execute the code written under HANDLER section.
These error handlers are common for the entire code but I need specific error handler for each different SQL.
e.g. like for INSERT and DELETE SQL, I would like to raise exception individually.
DECLARE EXIT HANDLER FOR SQLEXCEPTION LERROR: BEGIN SET vErrorMsg = 'Error: ' || 'SQLCODE: ' || SQLCODE || ' SQLSTATE: || SQLSTATE; INSERT INTO dvd1_work.procedure_error (procedure_nm, created_dttm, table_nm, sql_state_cd, message_txt) VALUES ('sp_ods1_ship_process', CURRENT_TIMESTAMP,'ods1_rerouted_shipment_temp', :SQLSTATE, :vErrorMsg); END LERROR; DELETE FROM dvd1_work.ods1_rerouted_shipment_temp; INSERT INTO dvd1_work.ods1_rerouted_shipment_temp VALUES (:iChildID, :iParentID);
Please let me know if you have any inputs on this.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|