Detecting a 'return status' from a CALL to a Stored Procedure (TD13)

Database
Teradata Employee

Detecting a 'return status' from a CALL to a Stored Procedure (TD13)

Is there a way to 'signal' to the calling environment (in this case BTEQ) the successful 'logical' execution of a Stored Procedure? e.g. in my particular case, I want to be able to let the calling BTEQ script to know that the execution of the stored procedure encountered some 'loigic error' and that the procedure needs to let the BTEQ script know not to continue. Sort of like calling a MACRO and checking for ERRORCODE after execution. Let's say that Iwrite some SQL to check the existance of a row in a table and it does not exist - i.e. a 'logical' error condition. In the Proc I then want to set some condition (Condition Handler??) and exit the procedure. In the BTEQ sript I then want to check the 'condition' or 'something' and if it is set (say to a value of 1) then quite the script signalling retcode of 12.

Is that possible in TD13.00 or higher? Thank you in advance for any info.

JK
2 REPLIES
N/A

Re: Detecting a 'return status' from a CALL to a Stored Procedure (TD13)

we're only on TD12, but I have an external stored procedure (in C) that allows me to set my SQLSTATE. When called from within a stored procedure, it allows me to do basically what you describe. I can set the error code and the error message. With proper error handling in the stored procedure in questions, you can check your logical condition and if it fails, call this SP to generate an error condition. Then BTEQ can check the ERRORLEVEL or ERRORCODE and exit appropriately.

It's a simple stored procedure. The code is here:

#define SQL_TEXT Latin_Text

#include < sqltypes_td.h >
#include < string.h >

void Set_SQLSTATE (
VARCHAR_LATIN *ErrorCode
,VARCHAR_LATIN *ErrorMessage
,int *indicator_ErrorCode
,int *indicator_ErrorMessage
,char sqlstate[6]
,SQL_TEXT fncname[129]
,SQL_TEXT sfncname[129]
,SQL_TEXT err_message[257] )
{
strcpy(sqlstate, (char *)ErrorCode);
strcpy((char *)err_message, (char *)ErrorMessage);
}
N/A

Re: Detecting a 'return status' from a CALL to a Stored Procedure (TD13)

ok, so the 2nd and 3rd lines should have less than and greater than symbols the .h file names.