SQL事务在存储过程的应用

-- =============================================
-- Author:        jf_ou
-- Create date: 2017-08-22
-- Description:    事务的使用1
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
AS
BEGIN
    --设置整体提交或整体回滚BEGIN
    SET XACT_ABORT ON
    DECLARE @ERROR INT = 0;
    BEGIN TRANSACTION
        IF(@ERROR = 1)
        BEGIN
            SELECT 'ERROR';
            --ROLLBACK后面必须加RETURN;且ROLLBACK必须在前面
            ROLLBACK TRANSACTION
            RETURN
        END
        ELSE
        BEGIN
            SELECT 'DOME'
        END
    IF(@@ERROR <> 0) 
    BEGIN
        ROLLBACK TRANSACTION
    END 
    COMMIT TRANSACTION

    --设置整体提交或整体回滚END
    SET XACT_ABORT OFF
END
GO

 

-- =============================================
-- Author:        jf_ou
-- Create date: 2017-06-19
-- Description:    事务的使用2
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    --设置整体提交或整体回滚BEGIN
    SET XACT_ABORT ON

    DECLARE @ERROR INT = 0;
    BEGIN TRY
        BEGIN TRANSACTION 
        --核心代码
        IF(@ERROR = 1)
        BEGIN
            SELECT 'ERROR';
            --ROLLBACK后面必须加RETURN;且ROLLBACK必须在前面
            ROLLBACK TRANSACTION
            RETURN
        END
        ELSE
        BEGIN
            SELECT 'DOME'
        END
        COMMIT TRANSACTION 
    END TRY 
    BEGIN CATCH
        Rollback TRANSACTION 
        --插入轨迹语句
    END CATCH
    
    --设置整体提交或整体回滚END
    SET XACT_ABORT OFF
END
GO