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

Question

I need to execute a password reset on a Sybase ASE dataserver based on certain conditions:

if validations_here
begin
    exec sp_password 'sso_passw', 'new_passw', @userid
end

sp_password might raise some errors, e.g. 10316 - "New password supplied is the same as the previous password". Although I couldn't find any documentation, I think they shouldn't be fatal errors and it should be possible to emulate them with raiserror.

Since it would be easier for the caller to handle it that way, I would like to get the error code and return it as part of a resultset, so I thought about SELECTing @@error. My code is as follows (I transcribed only those parts I think are relevant to the problem):

create procedure sp_desbloc_blanqueo_usr    
    @userid sysname,        
    @sso_pass varchar(20),  
    @new_pass varchar(20)   
as
begin
    declare @ret_code int
    declare @ret_msg varchar(100)
    declare @myerror int

    select @ret_code = 0, @ret_msg = 'OK'

    exec sp_password @sso_pass, @new_pass, @userid
    set @myerror = @@error 
    if @myerror <> 0
    begin
        select @ret_code = @myerror, @ret_msg = 'Error occurred changing password' 
        -- It would be nice to have the actual error message as well
        goto fin
    end

    fin:
    select @ret_code as ret_code, @ret_msg as ret_msg
end

However, whenever I execute the stored procedure, I get 0 as ret_code and OK as ret_msg (even if parameters to sp_password are wrong).

How can I "catch" the error code of sp_password from my stored procedure?

Possible duplicate of [Sybase: is it possible to set @@error](http://stackoverflow.com/questions/23631224/sybase-is-it-possible-to-set-error) — Mike Gardner, Oct 23 '15 at 12:09
@MichaelGardner I'm not trying to reset @@error. I want to get the raised error code, and prevent the SP to abend. Your referred question doesn't answer mine. — DiegoAlfonso, Oct 23 '15 at 14:31
Please reread the answer. You can not use raiseerror with a string. You must use sp_addmessage for the error text. — Mike Gardner, Oct 23 '15 at 14:34
Thank you, I see your point now. Since my test was inaccurate, I'll edit the question with the actual code. — DiegoAlfonso, Oct 23 '15 at 14:40
This may be helpful as well - https://scn.sap.com/thread/3812744 — Mike Gardner, Oct 26 '15 at 14:35

score 1 · Accepted Answer · answered Oct 26 '15 at 20:10

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.

It is a very good approach. This way I can at least notify the caller that there where errors. Is it possible to know which errors occurred? — DiegoAlfonso, Oct 28 '15 at 20:12
Unless it has changed in the newest versions, it's not possible from inside a stored procedure or T-SQL batch. If any, the last error may be still available from @@error, depending on whether or not it arose in the last statement inside the stored proc. An Open Client application (for instance, written in C) can obtain them; after all, this is what the isql tool did when it reported the results along with the errors. — Corral, Oct 30 '15 at 15:42

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

1 Answers1