Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 10 Dec 2008 @ 16:23:59 GMT


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


Subj:   Re: Exception handling for stored procedures and functions
 
From:   naomi

Sridhar,

We use SPs -- we call them from our front end application. I assume that's what you want to do. A typical SP in our system has as many IN parameters as it needs, and then 2 INOUT parameters:

     INOUT IO_VALIDATION_CDE SMALLINT,
     INOUT IO_ERRMSG VARCHAR(30)

At the start of the SP, we'd have this code:

     SET  IO_VALIDATION_CDE = 1 ;
     SET  IO_ERRMSG = 'Successful' ;

Then in the SP, after every SQL statement, there's logic like this (which varies depending on the situation):

     IF sqlstate <>  0
     THEN
          SET IO_VALIDATION_CDE = 0 ;
          SET IO_ERRMSG = 'Message that users will understand';
          LEAVE BeginSP;
     /* this is the name of the SP */
     END IF ;

You can check Teradata documentation to see what values sqlstate might potentially get set to, and finetune your message to the error code, or decide that certain error codes aren't really errors in that situation. For example, we use:

     IF SQLSTATE NOT IN ('00000', '02000') THEN

when we don't want the fact that a delete or update statement failed to find any rows to delete/update.


Hope that helps.

Naomi



     
  <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: 15 Jun 2023