Should “return” reset @@error?

Drew Montgomery brought the fact that Sybase ASE’s sp_password stored procedure raises the appropriate error message if it is unable to update the password of a login then returns "1" indicating a failure:

 /*
**  Encrypt and store the input @new_password.
**  @caller_password will be checked against the password of the caller.
**  set_password() builtin will print out nice messages.
*/
select @returncode = set_password(@caller_password, @new_password, @loginame, @immediate)
if (@returncode = 0)
begin
        /*
        ** 17720, "Error:  Unable to set the Password."
        */
        raiserror 17720
        return (1)
end
else
begin
        /*
        ** 17721, "Password correctly set."
        */
        exec sp_getmessage 17721, @msg output
        print @msg
        return (0)
end

Normally this would be fine but if you write a stored procedure wrapper for sp_password (or another ASE stored procedure), it is impossible to trap the exact error message from within your wrapper stored procedure.  The problem is that @@error is being reset by the return() operation, indicating that the return(), itself, resulted in no error.

I know this is the way it is now, but should it remain so?  I mean, should the execution of return() reset @@error if called in a stored procedure?

Granted, Sybase could probably correct their code by return(@@error) instead of returning a generic ‘1’ but is this a design flaw of the stored procedures or Sybase’s implementation of T-SQL?

I have to admit that I’m on the fence as to where the flaw resides other than it is a ‘Sybase issue’.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *