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’.