SQL Server Stored Procedure Combined with the transaction code

Copy codeThe Code is as follows:
-- Method 1
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [USP_ProcedureWithTransaction_Demo] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [USP_ProcedureWithTransaction_Demo]
GO
-- ===================================================== ======
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo: transactions used in Stored Procedures>
-- ===================================================== ======
Create PROCEDURE [dbo]. [USP_ProcedureWithTransaction_Demo]
As
Begin
SET XACT_ABORT ON
Begin Transaction
Insert Into Lock (LockTypeID) Values ('A') -- this statement will have an error. LockTypeID is of the Int type.
Update Lock Set LockTypeID = 2 Where LockID = 32
Commit Transaction
SET XACT_ABORT OFF
End
GO

-- Method 2
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [USP_ProcedureWithTransaction_Demo] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [USP_ProcedureWithTransaction_Demo]
GO
-- ===================================================== ======
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo: transactions used in Stored Procedures>
-- ===================================================== ======
Create PROCEDURE [dbo]. [USP_ProcedureWithTransaction_Demo]
As
Begin
Begin Transaction
Insert Into Lock (LockTypeID) Values ('A') -- this statement will have an error. LockTypeID is of the Int type.
Update Lock Set LockTypeID = 1 Where LockID = 32
Commit Transaction
If (@ ERROR <> 0)
Rollback Transaction
End
GO

-- Method 3
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [USP_ProcedureWithTransaction_Demo] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [USP_ProcedureWithTransaction_Demo]
GO
-- ===================================================== ======
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo: transactions used in Stored Procedures>
-- ===================================================== ======
Create PROCEDURE [dbo]. [USP_ProcedureWithTransaction_Demo]
As
Begin
Begin Try
Begin Transaction
Update Lock Set LockTypeID = 1 Where LockID = 32 -- an error occurs in this statement. LockTypeID is of the Int type.
Insert Into Lock (LockTypeID) Values ('A ')
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
GO

Exec [USP_ProcedureWithTransaction_Demo]