TRY/CATCH

Transaction in a TRY/CATCH

This will rollback both inserts due to an invalid datetime:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, 'not a date', 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION -- First Rollback and then throw.
    THROW
END CATCH

This will commit both inserts:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    THROW
    ROLLBACK TRANSACTION
END CATCH

Raising errors in try-catch block

RAISERROR function will generate error in the TRY CATCH block:

DECLARE @msg nvarchar(50) = 'Here is a problem!'
BEGIN TRY
    print 'First statement';
    RAISERROR(@msg, 11, 1);
    print 'Second statement';
END TRY
BEGIN CATCH
    print 'Error: ' + ERROR_MESSAGE();
END CATCH

RAISERROR with second parameter greater than 10 (11 in this example) will stop execution in TRY BLOCK and raise an error that will be handled in CATCH block. You can access error message using ERROR_MESSAGE() function. Output of this sample is:

First statement
Error: Here is a problem!

Raising info messages in try catch block

RAISERROR with severity (second parameter) less or equal to 10 will not throw exception.

BEGIN TRY
    print 'First statement';
    RAISERROR( 'Here is a problem!', 10, 15);
    print 'Second statement';
END TRY
BEGIN CATCH
    print 'Error: ' + ERROR_MESSAGE();
END CATCH

After RAISERROR statement, third statement will be executed and CATCH block will not be invoked. Result of execution is:

First statement
Here is a problem!
Second statement

Re-throwing exception generated by RAISERROR

You can re-throw error that you catch in CATCH block using TRHOW statement:

DECLARE @msg nvarchar(50) = 'Here is a problem! Area: ''%s'' Line:''%i'''
BEGIN TRY
    print 'First statement';
    RAISERROR(@msg, 11, 1, 'TRY BLOCK', 2);
    print 'Second statement';
END TRY
BEGIN CATCH
    print 'Error: ' + ERROR_MESSAGE();
    THROW;
END CATCH

Note that in this case we are raising error with formatted arguments (fourth and fifth parameter). This might be useful if you want to add more info in message. Result of execution is:

First statement
Error: Here is a problem! Area: 'TRY BLOCK' Line:'2'
Msg 50000, Level 11, State 1, Line 26
Here is a problem! Area: 'TRY BLOCK' Line:'2'

Throwing exception in TRY/CATCH blocks

You can throw exception in try catch block:

DECLARE @msg nvarchar(50) = 'Here is a problem!'
BEGIN TRY
    print 'First statement';
    THROW 51000, @msg, 15;
    print 'Second statement';
END TRY
BEGIN CATCH
    print 'Error: ' + ERROR_MESSAGE();
    THROW;
END CATCH

Exception with be handled in CATCH block and then re-thrown using THROW without parameters.

First statement
Error: Here is a problem!
Msg 51000, Level 16, State 15, Line 39
Here is a problem!

THROW is similar to RAISERROR with following differences:

• Recommendation is that new applications should use THROW instead of RASIERROR.
• THROW can use any number as first argument (error number), RAISERROR can use only ids in sys.messages view
• THROW has severity 16 (cannot be changed)
• THROW cannot format arguments like RAISERROR. Use FORMATMESSAGE function as an argument of RAISERROR if you need this feature.

Remarks

TRY/CATCH is a language construct specific to MS SQL Server’s T-SQL.

It allows error handling within T-SQL, similar to that seen in .NET code.