Raise Error and Return value with in a stored procedure

UDA

Raise Error and Return value with in a stored procedure

Hi,

I am trying to convert Stored procedure from Sybase to Teradata. Sybase has the option to raise error as well as return 0 or 1 for (success or failure).

The scenario is as follows,
1) SP1
2) SP2

Call of SP2 depends on the return value generated by SP1 ,for eg,

create procedure SP1(@z char(2))
as
if (char_length(@z," ") <> 2)
begin
raiserror 20000
return 1
end
return 0

exec @retcode=SP1 @z

IF @retcode = 0
exec @retcode=SP2 @z
ELSE
return 1

So here in this case, if the return value is 1, then the other stored procedure shall not execute and if its 0 then SP2 would get executed. This functionality is supported by Sybase.

I tried implementing the same functionality in Teradata using OUT parameter.However when the stored procedure SP1 executes successfully the out parameters set to 0, but if SP1 fails with error the OUT parameter doesn't return any value.

Please help in this regard.
5 REPLIES
Enthusiast

Re: Raise Error and Return value with in a stored procedure

Discussion is good and thanx for posting the informatrion
Teradata Employee

Re: Raise Error and Return value with in a stored procedure

There is a workaround for that, you must use an XSP named raisesqlstate for doing this:

here are examples :
REPLACE PROCEDURE p_test1()

BEGIN
DECLARE v_sqlstate CHAR(5);
DECLARE v_errmsg VARCHAR(256) CHARACTER SET LATIN NOT CASESPECIFIC;
SET v_sqlstate='T0001';
SET v_errmsg='Testmessage';
CALL SYSLIB.RaiseSQLSTATE(:v_sqlstate,:v_errmsg);
END;
/*
Will exit with return-code 7504 (UDF/XSP/UDM generated error) with an output-message containing the SQLSTATE value and the message, in this example:
"7504: in UDF/XSP/UDM SYSLIB.RaiseSQLSTATE: SQLSTATE T0001: Testmessage"
*/

REPLACE PROCEDURE p_test2()
BEGIN
DECLARE v_sqlstate CHAR(5);
DECLARE EXIT HANDLER
FOR SQLEXCEPTION, SQLWARNING
H1: BEGIN
SET v_sqlstate=SQLSTATE;
/*
Here one can add statements to do some clean-up and logging etc.
*/
CALL SYSLIB.RaiseSQLSTATE(:v_sqlstate,NULL); /* Re-raise captured/handled error*/
END H1;

CALL DBC.SysExecSQL( /*Some statement that fails*/'insert into my_missing_table values (1);');
END;
/*
Will exit with return-code and message of the failed statement, in this example:
"3807: p_test2:Object 'my_missing_table' does not exist."
*/

the XSP code (courtesy of Magnus Pierre) is :

#define SQL_TEXT Latin_Text
#include

extern void RaiseSQLSTATE(
SQL_TEXT in_sqlstate[6],
SQL_TEXT *in_errmsg[257],
int *in_sqlstate_isnull,
int *in_errormsg_isnull,
char sqlstate[6],
SQL_TEXT extname[129],
SQL_TEXT specific_name[129],
SQL_TEXT error_message[257]
)
{
if(*in_sqlstate_isnull==0) {
strcpy(sqlstate, in_sqlstate);
if(*in_errormsg_isnull==0) {
strcpy((char *)error_message, in_errmsg);
}
} else {
strcpy(sqlstate, "39001");
strcpy(error_message,"RaiseSQLSTATE: Invalid SQLSTATE specified");
}
return;
}

just compile it and add it to the syslib database.
cheers,
babak

Re: Raise Error and Return value with in a stored procedure

Hi ,

Thanks for your inputs for this issue. It would be very useful for me if you could explain it with an example like the one used in Sybase..
Teradata Employee

Re: Raise Error and Return value with in a stored procedure

This is interesting and might be what I'm looking for - i.e. send a return code that I can identify from a calling BTEQ script. Question thought, is SYSLIB.RaiseSQLSTATE() a TD provided proc? If so, where can we find it or is this something the GCS can set up for us? Thanks for any feedback - much appreciated.
Enthusiast

Re: Raise Error and Return value with in a stored procedure

Is there a link to download RaiseSQLSTATE?  I have worked with it (very successfully) on systems that have it, but cannot find it on this site?