Trasnsaction Commit and Rollbcak in SQL Server

Trasnsaction Commit and Rollbcak in SQL Server :

Generally Trasnsaction Commit and Rollbcak is used in SQL Server when user
want to perform Add, Modify and Delete operation but he don't want to submit
changes into the database until last statement execute successfully. Means  the
situation where either all or nothing.

Following store procedure is used to perform Add, Modify and Delete
operation on VisitorsPurpose table.

 Here OperationType 1 is  used to perform Add operation. OperationType 2
 is used to perform Modify operation and OperationType 3 is used to perform
 delete operation.

Here begin tran is used to begin the transaction. User can give any transaction
name but it must start with begin keyword followed by transaction name. Here 
I given tran as a transaction name.

After completion of all the operation successfully if @ErrorCode variable 
contains zero as a ErrorCode value then  commit tran is used which
commits all changes into the database. To commit transaction use commit 
keyword followed transaction name which is begin last.

If @ErrorCode variable contains non zero as a ErrorCode value then rollback tran
which  is used to undo all the operations performed within the trasaction. To
rollback transaction use rollback keyword followed by last begin transaction.

Also in insert statement I used with (ROWLOCK) keyword it means I want to
insert only single record at a time. If more than one records come at the same 
time then second record has to wait until first records gets inserted. It is 
used to avoid deadlock conditions but this may degreed performance.


Create procedure [dbo].[SpAmdPurpose]
(
  @OperationType as int   --1. Add Purpose 2. Modify Purpose. 3. Delete Purpose.
, @PurposeId int=0
, @PurposeName varchar(250)=null
, @CreatedBy varchar(100)=null
, @CreatedDate datetime=null
, @ModifiedBy varchar(100) = null
, @ModifiedDate datetime = null
)
as
begin
set nocount on

 declare @ErrorCode as int
 declare @Message as varchar(max)
 select @ErrorCode=0 , @Message=''

begin
 begin try
  begin tran  

--OperationType = 1 is used for adding new purpose.
 
 if(@OperationType = 1) 
         begin
  insert into VisitorsPurpose with (ROWLOCK)
  (
    PurposeName, CreatedBy, CreatedDate
  )
  values
  (
    @PurposeName, @CreatedBy, getdate()
  )
  set @ErrorCode=0
  set @Message='Purpose Created Successfully!'
 end

 --OperationType = 2 is used for modifying existing Purpose.

 else if(@OperationType=2)
 begin
  update VisitorsPurpose with (ROWLOCK)
  set
    PurposeName = @PurposeName
  , ModifiedBy = @ModifiedBy
  , ModifiedDate = getdate()
  where PurposeId=@PurposeId
   
  set @ErrorCode=0
  set @Message='Purpose Modified Successfully!' 
 end

--OperationType = 3 is used for Deleting Purpose

 else if(@OperationType=3) 
 begin
  if exists(select 1 from VisitorsPurpose where PurposeId=@PurposeId)
  begin
   set @ErrorCode=1
   set @Message='Purpose cannot be Deleted! It is already in use.'
  end
  else
  begin
   delete from VisitorsPurpose where PurposeId=@PurposeId
   set @ErrorCode=0
   set @Message='Purpose Deleted Successfully!'
  end
 end

 else
 begin
  set @ErrorCode=1
  set @Message='Invalid Operation Type!'
 end
  commit tran
 end try
 begin catch
   select @ErrorCode=110, @Message='Operation Failed due to invalid data. Please check details.'
   rollback tran
 end catch 
 end
 select @ErrorCode as [ErrorCode], @Message as [Message]
 set nocount off
end