Link to home
Start Free TrialLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on

Exception handling in Sybase

Hi Team,

I need to write a stored procedure to insert and delete a record in a table in sybase . Iam new to Sybase , below is the code I wrote in Oracle stored procedure
-- Oracle version
Create or replace procedure p1 (id number , status out number)
is
begin
insert into t1 values (id);

delete from t2 where empid=id;
commit;
exception
when others then
dbms_output.put_line(SQLCODE || SQLERRM);
rollback;
end;

I need to write the above logic in sybase , any help is really appreciated.  I have tried the below code  , need an help on the exception handling section

create procedure p1 (@id int , out @status int)
as
begin

insert into t1values (@id);
delete from t2 where empid=@id;
set @status=0;
commit;
-- Need help on how to write an exception block which can handle the error
end;
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Author-is this done?
Avatar of Swaminathan K

ASKER

Hi Pawan,

In my code , I have written the logic as below , the problem iam facing is if any error occurs in the select statement of the insert statement , it does not  go to the if condition to check the error status . Any way to handle it.

CREATE PROCEDURE P1 (@id int , out @status int)
as

BEGIN TRANS transx
begin

insert into t1
Select * from tablename;

insert into t2
Select * from tablename1;

end;
IF @@error<>0 BEGIN SELECT @Errorx = @@error GOTO failed END
begin
delete from t1;
delete from t2



end;

IF @@error<>0 BEGIN SELECT @Errorx = @@error GOTO failed END



COMMIT TRANS transx
RETURN 0

failed:
ROLLBACK TRANS transx
return @Errorx

END
How can we get error in the select statement. We have to fix the select statement. Could you please let me know what error are you getting?
No I want to handle error in an sql statement , for example formating errors etc. When it fires , I wan to go to exception handling section i.e label
Well thats not the errors. Can you give me an example of those errors?
BEGIN TRANS transx
begin

-- below the first column is integer and iam passing a n string value , is there way to handle it.

insert into t1
Select 'abc',a.* from tablename a;



insert into t2
Select * from tablename1;

end;
IF @@error<>0 BEGIN SELECT @Errorx = @@error GOTO failed END
begin
delete from t1;
delete from t2



end;

IF @@error<>0 BEGIN SELECT @Errorx = @@error GOTO failed END



COMMIT TRANS transx
But if you know that the first column should be INT why you are passing String. You can handle that on the UI itself. or before calling the stored procedure.