SQL TRY CATCH command is designed to control code flow in case that error situation occurs (error handling). This construction consists of blocks. It is also possible to use transaction (more in article on Transactions). If an error occurs in the first block – TRY..END like that is activated code in CATCH..END block.
TRY CATCH Syntax in SQL Server
The code below consists of
- TRY part: Script containing SQL DML or DDL (Create table, INSERT, UPDATE, etc.) is typically located in the first block. Eventually there is also a beginning of transaction BEGIN TRANSACTION and COMMIT command in the end (in case an error does not occur).
- CATCH part: Call out of function/s describing error is located here (error messages, severities etc.) with occasional error log and also the ROLLBACK command.
BEGIN TRY
BEGIN TRANSACTION
--SQL code here
COMMIT
END TRY
BEGIN CATCH
--Do something if error occurs
ROLLBACK
END CATCH
TRY CATCH Example with Error Handling Logging into SQL Table
Lets for example raise an Error message by dividing by zero. We will perform a ROLLBACK in CATCH part in case of error (which will occur). Following that, we will save properties of the error into temp table for further analysis:
SQL Script:
BEGIN TRY
BEGIN TRANSACTION
SELECT 1/0 AS [Result]
INTO #Temp;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
SELECT
ERROR_NUMBER() AS [ErrorNumber],
ERROR_SEVERITY() AS [ErrorSeverity],
ERROR_STATE() AS [ErrorState],
ERROR_PROCEDURE() AS [ErrorProcedure],
ERROR_LINE() AS [ErrorLine],
ERROR_MESSAGE() AS [ErrorMessage]
INTO #Error_Log;
END CATCH
Script in itself performed correctly. The error (Divide by zero error encoutered) have been caught and Error parameters are located in temp table #Error_Log
SELECT * FROM #Error_Log;
Let’s check that there is no transaction still opened and that transaction has been rolled back.
SELECT @@TRANCOUNT;
Note: If we would place ROLLBACK after Error log, then the logging itself would be in transaction. Following rollback would make us lose it. SQL TRY CACTH would however perform correctly and transaction would be withdrawn (sadly even with logging). That means that order of commands in CATCH matters.