We have a web application that all update, insert and DB operation procedure handle via SQL Server DB 2017. What is the best way to handle and get error (in Web-app or SQL server error handler?). for example a foreign key error occurred, is it better to handle it via DB or Code?

Update 1: for all operation from server side we have a procedure in DB. when user ran into error, handle of this error should be in DB or not (is it good architecture, or what is the pros and cons).

Update 2:

  -- do something, eg.
  INSERT INTO tabel1 (id, name) VALUES (10, 'sample data');

These errors tend to be unhandleable. In the sense that there's no automatic remedy you can apply. You simply have to log them and present the user with a friendly error message.

As such the Data layer should:

  1. Throw an exception,
  2. Let it bubble up to the application layer

the Application Layer should:

  1. Log the detailed error
  2. Create a "friendly error message" for the user to see
  3. Send the "friendly error message" to the user

Attempting to handle the error in the SQL, other than general use of transactions and rollbacks is unhelpful because:

  1. Creation of the "Friendly Message" is business logic. The general rule is to avoid putting business Logic in the database. Because it lowers performance

  2. Logging requires the full error message. Having your database write to a log file would be problematic and non performant

  • My concern is which one is better from performance or easiness? would you please describe a bit more? – Mouna Mokhiab Apr 15 at 9:11
  • for all operation from server side we have a procedure in DB. when user ran into error, handle of this error should be in DB or not? – Mouna Mokhiab Apr 15 at 9:13
  • 1
    you should prob add these comments, with more detail about what you tried and why it worries you, more about the nature of your application and the errors that are occuring etc to your question – Ewan Apr 15 at 9:14
  • I update my question. please help me to improve – Mouna Mokhiab Apr 15 at 9:23
  • I read your answer carefully, you means error should handle in DB and send result to business logic for error friendly message, you means if we didnt pass the error to business logic the performance is more better? am I right? – Mouna Mokhiab Apr 16 at 7:38

By far the most important part of Structured Exception Handling is the last part - the Handling.

Catch Exceptions where you can do something useful with/about them.

Ideally, that would be to take some corrective action, make the problem "go away" and then the caller never knows anything about it.

There is nothing the database can do to correct a Foreign Key error; indeed, it's the database that's raising the error, to tell the Application about the problem!

Agreed, logging Exceptions can be useful for diagnostic purposes, but it's better to let the client Application do that, because it will enrich the database error with contextual information of its own, for example, where in the program the failure occurred.

There are two kinds of Catch block that you should never write and you've demonstrated one of them:

  • Catch and do nothing. Never catch an Exception and do nothing at all with it. The Exception will be "swallowed" without trace by such a construct, potentially masking a much larger problem.
  • Catch and [only] rethrow. Never catch an Exception and simply rethrow the same Exception. Not only is it not "useful", but it defeats the purpose of Structured Exception Handling, where the Exception can propagate as far as it likes up the call stack until it finds something or someone that can handle it. Also, in many languages, throwing Exceptions is expensive (that "search" for a relevant handling routine can be slow!). That's not to say that you shouldn't catch an Exception and throw a different one at a "process boundary", say, at in a Public method of a shared library but, as a rule, no.
  • nice answer. but not clear me really which one is better? – Mouna Mokhiab Apr 15 at 16:38
  • It is highly unlikely that the database can "self-correct" errors, especially those that it has just thrown. As such, there is no benefit in catching them at the database level. Let them percolate up into the client application where you can do something "useful" with them. – Phill W. Apr 16 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.