T-SQL

TRY & CATCH pattern

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.

Leave a Reply

Your email address will not be published.