Archives of the TeradataForum

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

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

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>>