Skip to main content

What is the best model to catch DB side error? [Resolved]

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:

BEGIN TRY
  -- do something, eg.
  INSERT INTO tabel1 (id, name) VALUES (10, 'sample data');
END TRY
BEGIN CATCH
  THROW;
END CATCH

Question Credit: Mouna Mokhiab
Question Reference
Asked April 16, 2019
Posted Under: Programming
41 views
2 Answers

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.

credit: Phill W.
Answered April 16, 2019
Your Answer