Error Handling flexibility

Question

I am interested to see how other people have implemented flexible error handling. I will explain what I need specifically.

We all know that we can use exception wrapping (and catch it later where needed). However, what I would like to implement (and elegantly) is distinguishing the types of errors after the stored proc is invoked.

I have p_error field where stored proc dumps its errors. It could be validation error(where validating against DB can't be avoided), or authorization error (we do that in 2 places, one of which is DB), or a SQL error.
In short I need a pretty and elegant mechanism to distinguish between the type of error before throwing an exception.

Two approaches I thought of: 1) On the database level have 3 error fields: 1 for authorization, 1 for SQL error and 1 for any other errors. This could get hairy.

2) Creating an error struct (enum?) where error messages are stored and later can be compared against. Again, too wordy. what if a error message in db changes...to hard to maintain.

Any other ideas?

Answer 1

I don't see why people keep doing this p_error nonsense. At least with oracle (and I'm quite sure with sql server as well) the result of calling a stored procedure which throws an exception is a raised exception which contains far more information than this silly p_error field possibly can.

Again, my recent experience is simply with oracle but I would write a wrapper for all stored procedure calls (standard practice anyways) and inside it do something like this

try {
  RunMyStoredProcedure();
}
catch(OracleException e) {
  new OracleExceptionProcessor().HandleException(e);
}
//...
//...
class OracleExceptionProcessor {
  static List<int> _validationErrorCodes = new List<int> { 123, 456};
  static List<int> _authenticationErrorCodes = new List<int> { 789};

  public void HandleException(OracleException ex) {
    if(_validationErrorCodes.Any(c==ex.ErrorCode))
      throw new DatabaseValidationError(ex);
    if(_authenticationErrorCodes.Any(c==ex.ErrorCode))
      throw new DatabaseAuthenticationError(ex);
    throw new DatabaseSQLError(ex);
  }
}

Since I can't imagine the database having all that many validation or authentication error codes this should be pretty simple and easy.