Swaminathan K
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(SQLCO DE || 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;
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(SQLCO
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Author-is this done?
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
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?
ASKER
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?
ASKER
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
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.