Sqlserver 存储过程中使用事务

发布时间：2020-12-12 12:56:29 所属栏目：MsSql教程来源：网络整理

导读：?? --方式一 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 -- ==============

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!