Archives of the TeradataForum

Message Posted: Fri, 22 Oct 2004 @ 22:34:16 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

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.

Thanks
________________________

Roopali Doshi
Enterprise Datawarehouse Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>