Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63

    Unanswered: Error Handling in stored procedures

    Hey Everyone,

    Sorry if this has been asked before, seems the search is not responding to well today.

    Anyways, ran into the following problem. I have a stored procedure and it contains a query that in some cases can result in an error (converts a character field to a numeric field). Now I remember that after the query I can check @@error to see if it failed and react to it in a peacefull manner. It now seems though Sybase just stops executing my stored procedure and doesn't let me to do any cleanup resulting from the fault.

    I made a little test script to demonstrate my problem, hope someone can tell me how to do this correctly:
    Code:
    create table TMP_TEST (
      TMP_CHAR     varchar(250) null,
      TMP_NUMBER   numeric(15)  null
    )
    
    drop procedure stp_test
    go
    
    create procedure stp_test  as
    begin
      declare @lasterror integer
    
      insert TMP_TEST (TMP_CHAR, TMP_NUMBER) select "test",convert(numeric(15),"01abc")
      select @lasterror=@@error
      if (@lasterror<>0) begin
        return @lasterror
      end
    
      return 1000
    end
    go
    
    declare @status integer
    
    exec @status=stp_test
    
    select @status
    Greetz,

    Bastiaan Olij

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    Unfortunately, you must take some measures to
    avoid a conversion error. These errors are a different
    kind than execution errors such as "duplicate key".
    Instead, they are evaluated in an earlier stage by ASE,
    and handled in a way closer to syntax errors than to
    run-time errors.

    My advice: don't include statements prone to
    conversion errors in stored procedures. Try to
    isolate them in stand-alone SQL statements
    whose errors are handled by your application.

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •