Error Handling in Sybase

问题:

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?

回答1:

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