Sqlserver 存储过程中结合事务的代码

153 阅读 0 评论 101 点赞

我是靠谱客的博主儒雅柜子，最近开发中收集的这篇文章主要介绍Sqlserver 存储过程中结合事务的代码，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

复制代码代码如下:

 
--方式一 
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] 