SQL Server Performance

Why Generic Error Handling?

Discussion in 'General Developer Questions' started by cdykes, May 2, 2005.

  1. cdykes New Member

    I see a lot of code with generic error handling for every insert, update, or delete statement. What is the point of this code:

    begin tran
    insert X into table
    set @err = @@error
    if @err <> 0
    begin
    rollback
    raiserror or whatever else you want to do
    else
    commit tran
    end

    If there is a problem with the insert, sql server is going to stop the transaction and roll it back. It will also stop the stored procedure from continuing and display an error relating to what happened. It never reaches the check for @@error.

    Am I wrong in thinking that attaching this generic error handling to all insert/update/deletes is a waste of time? Are there times when things get through the insert w/out problem, but you want to cancel the sproc and rollback?

    Thanks,
    Chris
  2. benwilson New Member

    Hi Chris,
    I have been working on a stored procedure (SP1) lately that calls other stored procedures (SP2, SP3). In this case, if an error occurs in SP2 or SP3, control is returned to SP1 and it continues on from where it made the call unless i catch the error in SP2, roll it back, return the error to SP1 and then check the value returned. Not sure where else this is needed though! Also, if you dont catch the error code and do something with it (return it, etc) it can be hard to find whats not working i think!

    Ben
  3. FrankKalis Moderator

Share This Page