SQLServer-存储过程中的简单事务处理

  一. 添加记录

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

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

-- 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