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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fishinthewind/archive/2007/05/15/1610771.aspx