存储过程中的简单事务处理---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

二. 删除记录(同上)

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

三. 修改记录(同上)

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

四. 查询记录

   不需要事务支持.

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

-- Author:         <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

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

CREATE PROCEDURE [dbo].[GetAllJob]

AS

BEGIN

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

     -- interfering with SELECT statements.

     SET NOCOUNT ON;

   

    SELECT JobId, JobName

    FROM Job

END