Teradata stored procedure with multiple dynamic sql statements.

Database
Enthusiast

Re: Teradata stored procedure with multiple dynamic sql statements.

Hi All,

 

I am trying to put multiple "BEGIN ..END" as below

 

RESET:      BEGIN

L1: BEGIN

<Some query >

<insert into log_table>

END L1;

 

L2: BEGIN 

<Some query >

<insert into log_table>

END L2;

 

END RESET;

 

and i want to print all messages in log_table ,no matter if any step failed in between while calling the script.

IT should fail or skip any step if any of the step failed .

 

Please advsie if the above approach is correct or any other better way?

 

Regards,

Bhavini.

Teradata Employee

Re: Teradata stored procedure with multiple dynamic sql statements.

Simply having BEGIN/END blocks doesn't provide error handling or "skipping".

 

The exact details may vary depending on whether the SP is Teradata mode or ANSI mode, and what you ultimately do if something fails.

If you want processing to continue in-line after <Some query> fails, you need to DECLARE a CONTINUE HANDLER. If you declare it in the RESET block scope then it is available to nested L1/L2 also, or you can have handlers specific either or both inner blocks.

If you want to skip L2 if the query in L1 fails, you need some explicit code to either exit the SP or bypass the L2 block, whichever is the intent.

If you want to intercept the error, resume long enough INSERT to the log, but then pass the original error back to the caller at that point, then the handler will have to save the error information from the diagnostics area (since it will be cleared by the INSERT). After the write to the log has been committed, the code could use the saved information to SIGNAL the caller.