I saw a lot of articles that speak negatively about using try / catch in T-SQL and almost attribute it to antipatterns.

Is it fair?

  • Are there other ways of catching errors in tsql? In general, something tells me that your question is asking for a closing on "based on opinions, not facts" - Mike

1 answer 1

I have not met a single article with such reviews, now I’ve specifically looked for - I found one discussion - the author talks about one DBA expressing its negative attitude to this design - most of the comments in the discussion boil down to keeping this DBA away from the databases.

It is clear that, like any construction, it must be correctly understood and used - with the help of this block you can accidentally lose information about important errors that should have caused the program to crash.

The TRY / CATCH block is very useful in cases of using transactions, when you need to ensure integrity - at the beginning of the TRY block you start a transaction, at the end - end, and in the CATCH block you roll back the transaction (if we hit this block, it means something in the transaction went wrong) and return the error of the procedure.

Usually, many people sew such a block into a procedure template in order not to write such handlers every time. An example of such a pattern:

ALTER PROCEDURE <put procedure name and parameters here> AS BEGIN BEGIN TRY BEGIN TRANSACTION; -- Put your code here COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; 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 END; GO