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:

WORKING WITH TRY-CATCH BLOCK AND ERROR FUNCTIONS

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:

• ERROR_NUMBER(): The number assigned to the error
• ERROR_LINE(): The line number inside the routine that caused the error
• ERROR_MESSAGE(): The error message text
• ERROR_SEVERITY(): The error severity
• ERROR_STATE(): The error state number
• ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error

WORKING WITH TRY-CATCH BLOCK, ERROR FUNCTIONS AND @@TRANCOUNT BUILT-IN FUNCTION

@@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

WORKING WITH TRY-CATCH BLOCK AND THROW STATEMENT INSTEAD OF ERROR FUNCTIONS

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