Basic principles of error handling?

This is basic progra开发者_开发知识库mming 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?

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

If you are using SQL 2005 or 2008, you should use the new error handling constructs TRY/CATCH and not check @@error after every statement. This is the SQL 2000 way of doing things and it is a lot harder to read and manage.

You do not have to trap every error as already discussed, as a batch will not terminate on certain errors, but if you want to ensure that you abort a transaction on virtually anything, you should use SET XACT_ABORT ON at the beginning of your batch.

See anything by Erland Sommerskog online involving error handling. He is a renowned SQL Server MVP with error handling as a specialty.