Read | Practice | Advance
Here, I am going to cover the basics of Try-Catch error handling mechanism in T-SQL which is introduced in SQL Server 2005. It includes the usage of error functions to return information about the error using the Try-Catch block in the queries.
SQL Server uses the following basic syntax to capture errors in Transact-SQL statements in just two steps in three different working scenarios:
STEP 1: BEGIN TRY --Write necessary queries without transactions END TRY STEP 2: BEGIN CATCH SELECT DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); DECLARE @ErrorState INT = ERROR_STATE(); DECLARE @ErrorProcedure NVARCHAR(MAX) = ERROR_PROCEDURE(); DECLARE @ErrorLine INT = ERROR_LINE(); DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE(); PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); PRINT 'Actual error severity: ' + CAST(@ErrorSeverity AS VARCHAR(10)); PRINT 'Actual error state: ' + CAST(@ErrorState AS VARCHAR(10)); PRINT 'Actual error procedure: ' + @ErrorProcedure; PRINT 'Actual error line: ' + CAST(@ErrorLine AS VARCHAR(10)); PRINT 'Actual error message: ' + @ErrorMessage; END CATCH
In the scope of the Catch block the error functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose:
@@TRANCOUNT function is used to determine whether any transactions are still open. If there is a situation where the numbers of the opened transactions are at least one (in case of an error occurs) then roll back transaction can take place.
STEP 1: BEGIN TRY --Write necessary queries with transactions END TRY STEP 2: BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SELECT DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); DECLARE @ErrorState INT = ERROR_STATE(); DECLARE @ErrorProcedure NVARCHAR(MAX) = ERROR_PROCEDURE(); DECLARE @ErrorLine INT = ERROR_LINE(); DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE(); PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); PRINT 'Actual error severity: ' + CAST(@ErrorSeverity AS VARCHAR(10)); PRINT 'Actual error state: ' + CAST(@ErrorState AS VARCHAR(10)); PRINT 'Actual error procedure: ' + @ErrorProcedure; PRINT 'Actual error line: ' + CAST(@ErrorLine AS VARCHAR(10)); PRINT 'Actual error message: ' + @ErrorMessage; END CATCH
To simplify returning errors in a Catch block, SQL Server 2012 introduced the Throw statement. With the Throw statement, we don’t have to specify any parameters and the results are more accurate. You should simply include the statement as it is shown in the Catch block.
STEP 1: BEGIN TRY --Write necessary queries with/without transactions END TRY STEP 2: BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION THROW END CATCH
nice article posting