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()
DECLARE rowcount INTEGER DEFAULT 0;
DECLARE my_condition CONDITION FOR SQLSTATE value '45000';
DECLARE EXIT HANDLER FOR my_condition
/* 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*/
/* Since the rowcount default value = 0, this logic will always signal condition */
IF rowcount = 0
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
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.