1. Home
  2. Categories
  3. Coding Help
  4. RAISERROR all the time?

RAISERROR all the time?

  • W

    I have a manager that is asking us to put the following code after each statement in our stored procs(DB is Sybase);

     IF @@error != 0
    begin
        select @returnCd = 250xx
        RAISERROR @returnCd "Error xxx "
        return -1
    END

     The question is, is this needed for everything? id think if there was some kind of error doing a select/insert/delete an error would get thrown without me checking the error code. Shouldnt RaiseError be used for capturing errors we know not to be true? like getting no results where results are expected? Is this a common practice?

    Thanks 

     

    0
  • V

    Your manager is wasting your time. You are right that 'real' errors will drop out before it even gets to your @@error test. Very easy to prove this - for example write a procedure that inserts into a table, ignoring a not-null column.

    create table xx_test_table (
        id   int      not null,
        val1 char(10) not null)
    go

    create proc xx_test
    as

    insert into xx_test_table ( id )
    select 1

    if @@error <> 0
    begin
      raiserror 20000 'nonsense'
      return -1
    end
    go
    exec xx_test
    go

    You should use raiserror for generating errors out of business exceptions that *you* specify, exactly as in 'no rows returned', 'wrong trade type' etc., so that calling programs/procedures can detect your return code -1 and catch the error message. Also, I wouldn't bother with clever numbers in raiserror, just use 20000 every time, and a (unique & meaningful) message. I wouldn't waste my time maintaining/interpreting error codes.
    My qualifications? Sybase almost exclusively since 1989. Regards.

    0
  • D

    Managing a list of distinct error codes (master..sp_addmessage, I think) has its uses, particularly if you're using a client library that can access the errors collection after calling the procedure. The .NET SqlCommand/SqlConnection package does this nicely. Just catch SqlException, then check ex.errors[0].number or what have you. Sorta handy if you need to tailor your front end to gracefully bail out.

    Plus you can use printf-like patterns in the error message, like "Cannot find uid %d" and pass them in at runtime. Makes isqlw testing and debugging sessions a little more sane. :-)

    0
  • V

    Re: RAISERROR all the time? IMPORTANT CHANGE OF MIND!!

    OK I want to revoke part of what I said above. The example I quoted is true, but it is a bad example. If, for the above table with 2 non-null columns, you do :

    insert into xx_test_table ( id ) select 1
    then that will bomb straight out of your procedure, so no error checking is meaningful. If you do
    insert into xx_test_table(id, val1) select 1, null
    then that is in effect the same, but IN THIS CASE, SYBASE WILL GENERATE AN ERROR MESSAGE, SET @@error NON-ZERO AND CONTINUE TO SUBSEQUENT STATEMENTS. i.e. you should then test @@error and return if not zero. Still no need to do raiserror though. This is true for any constraint/consistency type error.
    Apologies for previous inaccuracy.

    update fails...

    0
Log in to reply