Clever Geek Handbook

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.

Questions:

  • Is there an @@ error here? I ran this SP and it never entered error handling
  • How to handle these error scenarios?
+3


source to share


3 answers


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.

0


source


Is your mistake intimidating?



create procedure test_launcher_fail_wrapper
as
begin

  declare @database_err int

  set @database_err = 0

  select convert(numeric(2),1234345)

  set @database_err = @@error

  if @database_err <> 0
  begin
    PRINT  'SP failed to execute'
    return 1
  end

end
0


source


@@ 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 ?

Example:

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
0


source






More articles:


All Articles