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).
BEGIN TRY -- do something, eg. INSERT INTO tabel1 (id, name) VALUES (10, 'sample data'); END TRY BEGIN CATCH THROW; END CATCH
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 throw and bubble up the exception and the application layer should handle the logging and friendly error presentation.
Attempting to handle the error in the SQL, other than general use of transactions and rollbacks is unhelpful.
Creation of the "Friendly Message" is business logic, may require translations etc and will lower performance of your DB
Logging requires the full error message and again you don't want to have to worry about your database writing to log files when it should be processing the next transaction.