How to use Try Catch Block for SQL Transactions in SQL Server 2005?

Modern programming languages like C#, java made the exception handling simpler by adding try catch mechanism. The back end systems employed a completely different approach to handle errors and exceptions which is sort of complicated since it mostly deals with error ids and numbers.

Microsoft brought the use of try catch mechanisms in SQL Server 2005 for efficient and easy error handling. Like any other statement, the try catch also has Begin -End syntax. Refer below,

BEGIN TRY

//SQL statements

 END TRY
 BEGIN CATCH
//Hanlde errors

 END CATCH

The below code snippet will help us to implement a simple transaction and rollback transaction using Try Catch block in SQL server 2005.

CREATE PROCEDURE DoComplextTrans
(@ID int)
AS
BEGIN

 BEGIN TRY

  BEGIN TRANSACTION   

  
  /* Your transactions*/

  
  COMMIT
 END TRY
 BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
  /* Error occured*/
  ROLLBACK
  END

  
 END CATCH

END

Read here to know more about http://msdn.microsoft.com/en-us/library/ms187967.aspx