-- 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 cause an error. The 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 cause an error. The 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 -- This statement will cause an error. The 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 ]