频道导航

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

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了Sqlserver 存储过程中结合事务的代码，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

<div class="codetitle"><a style="CURSOR: pointer" data="38354" class="copybut" id="copybut38354" onclick="doCopy('code38354')"> 代码如下:

<div class="codebody" id="code38354">
--方式一
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:
-- 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:
-- 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:
-- 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]

原文链接：https://www.f2er.com/mssql/64122.html

事务事务存储过程