存储过程中的简单事务处理---SQLServer2005系列

一. 添加记录

?? 需要事务支持,如果上下文环境中不存在事务,新启动一个事务,否则利用已存在的事务.

-- =============================================

-- Author:???????? <Author,Name>

-- Create date: <Create Date,>

-- Description:??? <Description,>

-- =============================================

CREATE PROCEDURE [dbo].[AddJob]

???? @jobName nvarchar(50),

???? @added bit output

AS

BEGIN

???? -- SET NOCOUNT ON added to prevent extra result sets from

???? -- interfering with SELECT statements.

???? SET NOCOUNT ON;

?

??? DECLARE @errorCode int

??? SET @errorCode = 0

?

??? --Transaction identity. 1:Created a new transaction. 0:Use old transaction.

??? DECLARE @tranStarted bit

??? SET @tranStarted = 0

?

??? --Create a new transaction if there is not a transaction.

??? IF( @@TRANCOUNT = 0 )

??? BEGIN

??????? BEGIN TRANSACTION

??????? SET @tranStarted = 1

??? END

??? ELSE

??????? SET @tranStarted = 0

?

??? --Adding operation.

??? INSERT INTO dbo.Job(JobName)

???????? VALUES (@jobName)

?

??? IF ( @@ERROR <> 0 )

??? BEGIN

??????? SET @errorCode = -1

??????? GOTO Cleanup

??? END

?

??? IF ( @tranStarted = 1 )

??? BEGIN

??????? SET @tranStarted = 0

??????? COMMIT TRANSACTION

??? END

???

??? SET @added = 1

??? RETURN(0)

?

?--Error handler.

?Cleanup:

??? IF( @tranStarted = 1 )

??? BEGIN

??????? SET @tranStarted = 0

??????? ROLLBACK TRANSACTION

??? END

?

??? SET @added = 0

??? RETURN @errorCode??

END

（编辑：李大同）

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