How to do error handling in a stored procedure
I am using Sybase ASE 12.5 at the moment. I have below code:
create procedure test_launcher_fail_wrapper as begin select convert(numeric(2),1234345) if @@error != 0 begin select "SP failed to execute" return 1 end end
Here I am trying to convert a very large value / count (1234345) to numeric size 2. This is not possible and generates an error.
- Is there an @@ error here? I ran this SP and it never entered error handling
- How to handle these error scenarios?
I handle error handling in procs in a similar way to error handling in applications - if you have the ability to inject some actual value by addressing the error, then by all means, do it, but if there is nothing you can do to help then you it's better to just let it go.
As an example of adding a value, I have one or two procs that add contextual information to the error message, such as a list of id values that conflict with the update operation. In this particular case, I know that the above consumer proc will log this error and the text will be available to the operator, who will find this information useful when debugging the problem. I also know that while this condition is a real error, it is known to happen from time to year and is worth trying to format the error.
source to share
@@ error is the way to go, but be careful as:
Every Transact-SQL statement, including print statements and tests, throws the @@ error, so the status check must immediately follow the batch that is set to succeed.
As far as a suggestion for managing errors in scenarios like this, have you considered using raiserror ?
create procedure proc1 as begin select convert(numeric(2),1234345) if @@error <> 0 begin raiserror 20001 "Error during convert in proc1" return 1 end end
source to share