Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Stored Procedure: Error Raising
 
From:   Doshi, Roopali

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016