Calling External Stored Procedure from EXIT Handler

Database
Teradata Employee

Calling External Stored Procedure from EXIT Handler

Problem statement:

We want to set custom SQLSTATE in Teradata stored procedures. The only way to set custom SQLSTATE value is through external stored procedure.

To set the custom SQLSTATE, we have defined a condition and condition handler in our stored procedure. In our business logic we signal the condition, and in condition handler we log the error details and call the external stored procedure to set the custom SQLSTATE and return it to the calling routine of internal stored procedure.

However, the SQLSTATE value does not return as expected. In fact the condition handler’s SQLSTATE (45000, look at code below) is returned.

Following is the snippet of my stored procedure code.

                  Create Procedure my_internal_procedure()

                  BEGIN

                                                DECLARE rowcount INTEGER DEFAULT 0;

                                                DECLARE my_condition CONDITION FOR SQLSTATE value  '45000';

                                                DECLARE EXIT HANDLER FOR my_condition

                                                BEGIN

                                                                /* My logging logic and some housekeeping */

                                                                /* Calling external stored procedure */

                                                                /* This should return the define custom SQLSTATE  value and


      raise it to the calling routine of my_internal_procedure*/

                                                                call  my_external_procedure(‘error_code’);                                      

                                                END;

                                                /* Since the rowcount default value = 0, this logic will always signal condition */

                                                IF rowcount = 0

                                                THEN

                                                                SIGNAL my_condition;

                                                END IF;

                  END;

Now, our issue is that the call to external stored procedure from exit handler doesn’t result in the required custom SQLSTATE. If we call the external stored procedure from any other part of the code, then the custom SQLSTATE is set successfully and is returned to the calling routine of internal_procedure. Which means that my external procedure is working OK and we can throw a custom SQLState by calling it from internal stored procedure.

Now, the question we have are

  1. Can we call an external stored procedure from an exit handler to raise our custom exception?
  2. What is the difference between calling external procedure from EXIT HANDLER and calling the same from outside the HANDLER?

We have also tried the it by not specifying the SQLSTATE value ‘45000’ with the condition. But in that case the external SP call from EXIT HANDLER gives us the error ‘my_condition is unhandled’.

We could not find any clue about it from the relevant Teradata documentation.

It’ll be great if anybody can help us out on this.

Regards

1 REPLY

Re: Calling External Stored Procedure from EXIT Handler

Any news about this question?

Regards,

Matteo