Copy Code code as follows:
--Way One
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: Using transactions 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 type int
Update Lock Set Locktypeid = 2 Where lockid = 32
Commit Transaction
SET Xact_abort off
End
Go
--Way Two
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: Using transactions 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 type int
Update Lock Set Locktypeid = 1 Where lockid = 32
Commit Transaction
If (@ @ERROR <> 0)
Rollback Transaction
End
Go
--Way Three
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: Using transactions in Stored procedures >
-- =============================================
Create PROCEDURE [dbo]. [Usp_procedurewithtransaction_demo]
As
Begin
Begin Try
Begin Transaction
Update Lock Set Locktypeid = 1 Where lockid = 32--This statement will be faulted, locktypeid to type int
Insert into Lock (Locktypeid) Values (' A ')
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
Go
Exec [Usp_procedurewithtransaction_demo]
