All Forums UDA
Abhisek 2 posts Joined 12/09
03 Dec 2009
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) SP2Call 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 0exec @retcode=SP1 @zIF @retcode = 0 exec @retcode=SP2 @zELSE return 1So 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.

melina386 8 posts Joined 11/09
10 Dec 2009

Discussion is good and thanx for posting the informatrion

Vador 36 posts Joined 08/07
11 Dec 2009

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

Abhisek 2 posts Joined 12/09
14 Dec 2009

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..

jkwight 14 posts Joined 06/10
02 Jun 2010

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.

JK - TD-PS
Arizona Territories

JimTepin 8 posts Joined 08/11
01 Apr 2013

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?

You must sign in to leave a comment.