Error Handling in Sybase

Question

Is there a way to handle errors in SYBASE, such as the TRY-CATCH block you can use in MS SQL Server, Oracle, etc?

I've searched the web and the only option I found was the global variable @@error, but it didn' work as I expected, for example, the following code:

begin tran

update table1
set name = 'new name'
where name = 'old name'

update table2
set id = 1 
where id = 30
-- suppose id has a unique constraint and there's already a row with id = 1

IF @@error = 0
begin
    print 'commited'
    commit
end
else
begin
    print 'rolled back'
    rollback
end

The will indeed rollback somehow, because the name I've changed on table1 keeps the old value as I've tested here, but it doesn't print the messages, or execute any instructions I put after the instructions that causes the error

Can anyone help me in this? Do you know how does Sybase error handling actually works?

you could setup an if block on the update so that there wouldn't be an error. if(not exists(select 1 from table2 where id = 1))begin/* your update code here*/end — scrappedcola, Oct 09 '12 at 22:11
Thanks for your reply, but I need to do something like that in a script that will execute a lot of inserts/updates and a lot of constraints could be violated, so the validations would take a huge amount of code. Also, without these validations the script already have more than 1200 lines of code, so a try-catch block or something like that would be more fitting, but as it seems it's not possible, so I'm thinking about just letting sybase roll back the transaction in case of error by itself. — drigoangelo, Oct 10 '12 at 12:23
Try taking a look at the **'sp_addmessage'** command that allows you to define your own error messages, and the **'raiserror'** command that allows you to call those errors, and set the value of @@error. — Mike Gardner, Oct 10 '12 at 13:02
In my documentation on sybase-iq, I read there is an `exception` statement.with the function of a `catch` block. — Dirk Horsten, Mar 14 '16 at 10:42

Robert · Accepted Answer · 2012-10-11T07:22:46.637

1st solution.

You can't catch an exception this way on Sybase. Before you update you have to check data:

if not exists
(
  select 1 from table2
  where id = 1
)
begin
  update table2
  set id = 1 
  where id = 30
end
else
begin
  print 'rolled back'
  rollback
end

2nd solution.

You can also put an update command to procedure, then you can catch an exception. Create procedure:

create procedure myproc
as
begin
  update table2
  set id = 1 
  where id = 30
end

and run it as below:

begin tran

update table1
set name = 'new name'
where name = 'old name'

exec myproc

IF @@error = 0
begin
    print 'commited'
    commit
end
else
begin
    print 'rolled back'
    rollback
end

Error Handling in Sybase

1 Answers1

Linked