SQL Server - Error handling and Transactional handling in T-SQL programming

Below is an example showing how to use Error handling as well as Transactional handling in TSQL programming. 

1) When SQL server starts executing the below SQL statement , it will start a transaction then it will go into the TRY block where our code starts executing. if SQL statement in TRY block executed successfully then it wont go to CATCH block and complete the transaction.

2) In case SQL statement in TRY block failed then it will go to CATCH block to catch the error. In this case, Transaction will be rollback.

3) @@TRANCOUNT variable checks if transaction is active or not. @@TRANCOUNT = 1 meaning transaction is active

4) Error handling & Transactional handling is very useful when we do INSERTION,DELETION & Update operation over the data.

BEGIN TRANSACTION;

BEGIN TRY
	-- Updating table  
	UPDATE Product
	SET ProductColor = 'Red'
	WHERE ProductID = 980;
END TRY

BEGIN CATCH
	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;

	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION;
GO