TRY/CATCH
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.
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.