Home sql Using Try / Catch in MS SQL Server

Using Try / Catch in MS SQL Server




saw many articles that negatively respond to TRY / CATCH use in T-SQL and almost believes to antipatterns.

Is this right?

Answer 1, Authority 100%

I have not met a single article with such reviews, now I specially searched – I found one discussion – the author tells about one dBA, expressing his negative attitude to this design – most comments in the discussion are reduced to what to keep such dubs away from databases.

It is clear that like any design, it must be correctly understood and used – with this block, you can accidentally lose information about important errors that would have to lead to a program.

TRY / CATCH unit is very useful in the use of transactions when you need to provide integrity – at the beginning of the TRY unit you start the transaction, at the end – you finish, and in the Catch block, you roll back the transaction (if we hit this block, it means that then in the transaction went wrong) and return the error of the procedure.

Usually such a block, many are sewn into the procedure template, so as not to write similar handlers each time. An example of such a template:

alter procedure & lt; Put Procedure Name and Parameters Here & GT;
 Begin Try.
  Begin Transaction;
  - Put Your Code Here
  COMMIT Transaction;
 End Try.
 Begin Catch.
  If @@ TRANCOUNT & GT; 0.
  Declare @errornumber int = error_number ();
  Declare @errorline int = error_line ();
  Declare @errorMessage Nvarchar (4000) = error_message ();
  Declare @errorSeverity int = error_severity ();
  Declare @errorState int = error_state ();
  Print 'Actual Error Number:' + Cast (@errornumber As Varchar (10));
  Print 'Actual Line Number:' + Cast (@errorline AS VARCHAR (10));
  Raiserror (@errormessage, @errorSeverity, @errorState);
 End Catch.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions