TRY & CATCH should be used where reasonable to prevent unhandled exceptions in T-SQL code and routines. It’s prepared for copy/paste purposes for everyday use.
We may have two basic types of pattern:
- The new one using THROW keyword
- The legacy one with RAISERROR()
THROW version:
BEGIN TRY
BEGIN TRAN
PRINT 1/0 -- "Divide by zero error encountered." error
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
;THROW
END CATCH
IF @@TRANCOUNT > 0
ROLLBACK
GO
RASIERROR() version:
BEGIN TRY
BEGIN TRAN
PRINT 1/0 -- "Divide by zero error encountered." error
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @Message NVARCHAR(4000)
SET @Message = ERROR_MESSAGE()
RAISERROR (@Message, 16, 1);
END CATCH
IF @@TRANCOUNT > 0
ROLLBACK
GO
They both work pretty well for most of the usage scenarios but there is one very important difference to remember: The THROW statement is terminating the batch where it fires an error!
Let’s see it in action:
BEGIN TRY
BEGIN TRAN
PRINT 1/0 -- "Divide by zero error encountered." error
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
;THROW
SELECT 'This Wan''t be executed 1'
END CATCH
SELECT 'This Wan''t be executed 2'
GO
Once the THROW statement was executed it has also terminated the batch and SELECT statements weren’t executed. If we will run the same with RAISERROR() the result is different:
BEGIN TRY
BEGIN TRAN
PRINT 1/0
END TRY
BEGIN CATCH
DECLARE @Message NVARCHAR(4000)
SET @Message = ERROR_MESSAGE()
RAISERROR(@Message, 16, 1);
SELECT 'This is executed 1'
END CATCH
SELECT 'This is executed 1'
GO
Same time the error message will be fired in the Messages window.
This is a very important difference that can drastically affect more complex T-SQL logic especially in the case of nested stored procedures or retry logic patterns.
For more details see:
Even if it looks to be easy, using TRY … CATCH is very complex when the type of fired error or other conditions came in the game. The best article ever written on this topic is by Erland Sommarskog. I’m strongly recommending read it thru.