Basic principles of error handling?

Question

This is basic programming question rather than a specific SQL Server question. I would like to know what are basic principles in error handling? When error is occurred, should the code always throw exceptions? Should exception always terminate the procedure or batch?

Answer 1

How you handle an error can often depend on the context. There are certain errors you may expect. An example of this could be something like a foriegn key violation. In this case you may just wish to log the error (potentially logging this as informational as opposed to a proper error) and return a user friendly message to the UI to notify the user that a problem occurred. Other errors are unexpected. These should probrably also be logged and reported to the user with potential further steps to get the exception trace to the development team (i.e. via email etc).

Many modern programming languages these days have structured exception handling capabilities which makes trapping and logging exceptions pretty straight forward. There are also a number of logging frameworks to help with the logging. Good logging frameworks in the .Net arena include log4net, nLog and elmah. There should be loads of examples on the web for all of these logging frameworks.

As far as SQL Server is concerned the error handling capabilities aren't that advanced.

The common pattern for handling errors in a stored proc would be something like

create procedure [dbo].[test_dosomework]
(
  @param1 int,
  @param2 varchar(10)
)
as
begin
  declare @errorCode int

  select @errorCode = 0

  insert table1 (aValue)
  values (@param1)

  select @errorCode = @@ERROR

  if @errorCode = 0
  begin
    insert table1 (aValue2)
    values (@param2)

    select @errorCode = @@ERROR
  end 
  return @errorCode
end

In this case if an error occurs in either of the insert statements the @ERROR will return the error number, @errorCode will be set with this error number and the return value of the stored procedure will be that of the error number. The return value will be 0 for