Sybase: How to Set @@Error

Sybase: is it possible to set @@error

In Sybase ASE you can't directly set @@error or use raiseerror with a string.

Use sp_addmessage to add custom error numbers and messages to your database. Once the error is listed, you can then reference it using the raiserror error_#

Sybase ASE reserves error numbers 20,000 and below for system use, so user defined errors can start at 20,001

More detailed information on raiserror can be found in the documentation.

Handling error from one stored procedure into another in Sybase ASE 15.0

Many "sp_" stored procedures set a nonzero return code when something goes wrong. Usually it is better to handle this return code than trying to catch errors raised inside the stored procedure. IIRC, this catching would not be possible with Transact-SQL; a 3rd generation language such as C would be required.

To get the return code of myproc stored procedure into variable @myvar, the syntax is

exec @myvar = myproc [arguments] 

A simple example with sp_password:

declare @spreturn int  
exec @spreturn = sp_password 'notmyoldpw', 'notmynewpw'  
print "Return from sp_password is %1!", @spreturn  
go

Server Message:  Number  10315, Severity  14  
Server 'SDSTRHA01_SY01', Procedure 'sp_password', Line 148:  
Invalid caller's password specified, password left unchanged.  
Server Message:  Number  17720, Severity  16  
Server 'SDSTRHA01_SY01', Procedure 'sp_password', Line 158:  
Error:  Unable to set the Password.  
(1 row affected)  
Return from sp_password is 1  
(return status = 1)  

The int variable @spreturn defined in the first line got sp_password return code, whose value was one as shown by (return status = 1) in the last message line. The reason why it was not zero is clear: there were two errors inside sp_password, 10315 and 17720. The point is to focus in this nonzero return code and ignore 10315 and 17720. In your stored proc, @spreturn ought to be checked against zero. If zero it ran OK, else something failed.

Error Handling in Sybase

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 not set upon following error in Sybase ASE

Apparently there is no way to catch the specified error using the global @@ERROR variable.

Get System Error Message In Sybase

I don't know how to take specify error message - I think it's not possible. Maybe below query will cover your needs. It return and pattern message for example Must declare variable '%.*s'. insetad of Must declare variable 'fake variable'.

SELECT description 
from master..sysmessages where error = @@error

@@error variable change every time you make an operation so you need to use local variable for example @err. In your code should be like this.

declare @err int,
        @msg varchar(255)

Insert into A
Select top 250 id from C
inner join D
on c.id = D. id

select @err = @@error

IF (@err != 0)
BEGIN
    SELECT @p_err_code = 1
    SELECT @p_err_desc = "Error while inserting records into #PAR_PROVIDERS."

    SELECT @msg = description 
    from master..sysmessages where error = @err

    DROP TABLE #PAR_PROVIDERS
    RETURN 1
END

Sybase power function returns an empty result set (Truncation error)

I've discovered that it works if both inputs are floating point.

In other words, this works:

select power(1+(8.0/12), 60.0)

how to get an error message and save it in a table in sybase?

Here is the solution. It is little tricky. Make the query dynamic, so that Syabse wont complain about the syntax error while compile. You compilation will be fine and you will get your desire result in run-time.

create procedure getTitle @title_id VARCHAR(9)
as

declare @error  int,
@title varchar(100)

begin

declare @my_query varchar(500)
select @my_query = "insert into tab1 (CODE) VALUES (1) jhghjghj"  
exec (@my_query )

SELECT @title = description from master..sysmessages where error = @@error
update ex_employee set info = @title
commit
print "%1!", @title
end
go

@@ERROR 0 in trigger

I've used for testing Sybase Central v.6.0
And it does't show any PRINT results if error occurs
When I've used QTODBC tool for connecting to Sybase ASE server, it threw me exception and printed 'ERROR!!!' message

So, this is the feature of Sybase Central environment
Actually, trigger resumes working when any error occurred

---

Related Topics