Sqlserver 存儲過程中結合事務的代碼

2020-07-25 13:25:49

字體：大中小

來源：轉載

供稿：網友

復制代碼代碼如下:

 
--方式一 
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:存儲過程中使用事務> 
-- ============================================= 
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo] 
As 
Begin 
SET XACT_ABORT ON 
Begin Transaction 
Insert Into Lock(LockTypeID) Values('A')--此語句將出錯，LockTypeID為Int類型 
Update Lock Set LockTypeID = 2 Where LockID = 32 
Commit Transaction 
SET XACT_ABORT OFF 
End 
GO 

--方式二 
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:存儲過程中使用事務> 
-- ============================================= 
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo] 
As 
Begin 
Begin Transaction 
Insert Into Lock(LockTypeID) Values('A')--此語句將出錯，LockTypeID為Int類型 
Update Lock Set LockTypeID = 1 Where LockID = 32 
Commit Transaction 
If(@@ERROR <> 0) 
Rollback Transaction 
End 
GO 

--方式三 
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:存儲過程中使用事務> 
-- ============================================= 
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo] 
As 
Begin 
Begin Try 
Begin Transaction 
Update Lock Set LockTypeID = 1 Where LockID = 32--此語句將出錯，LockTypeID為Int類型 
Insert Into Lock(LockTypeID) Values('A') 
Commit Transaction 
End Try 
Begin Catch 
Rollback Transaction 
End Catch 
End 
GO 

Exec [USP_ProcedureWithTransaction_Demo] 