关于SQLSERVER 事物的运用(3)嵌套事物以及嵌套存储过程的异常的处理

概述：这一部分才是应用的重点，掌握了前两部分，基本上可以应付80%的应用。存储过程为什么要嵌套，我们会把一些业务功能进行分离，解耦，可以单部执行(如PA)，也可以以封装的方式执行(如PB)，就涉及到PB过程调用PA存储过程。重点是无论是在客户断上单次执行PA,执行PB.还是在查询分析器里执行PA,PB，都以事物的方式进行，并反馈错误的信息

本部分只是实现以CATCH的方式捕获，假设PA的代码如下供PB调用，并返回@OUTPUT值

CREATE PROCEDURE PA (
  @P1 VARCHAR(30)='',
  @P2 VARCHAR(30)='',
  @OutPut VARCHAR(300)='' OUTPUT
	)
AS
BEGIN
 DECLARE @ERR VARCHAR(300)
     /*============================================================
     STEP 1:有效性检查
      ============================================================*/
     IF(1=1)
     GOTO ERR
     
     /*============================================================
     STEP 2:有效性检查
      ============================================================*/
          
     /*============================================================
     STEP 3:有效性检查
      ============================================================*/

 BEGIN TRY 
 BEGIN TRAN;
     /*============================================================
     STEP 1:执行更新
      ============================================================*/
     
     /*============================================================
     STEP 2:执行更新
      ============================================================*/
     
     /*============================================================
     STEP 3:执行更新
      ============================================================*/
 SELECT @OUTPUT=’AAAAAA’
 COMMIT TRAN;
 RETURN 1                     --//返回值
 ERR1: 
       RAISERROR(@ERR,16, 1)  --//自定义错误，Catch捕获
 END TRY
 BEGIN CATCH
     IF @@TRANCOUNT>1          --//如果是嵌套的过程，必须COMMITTRAN,以减少当前计数,
       COMMIT   TRAN ;        --//一个存储过程只能有一个事物
      ELSE IF @@TRANCOUNT=1
       ROLLBACK TRAN;         --如果不是嵌套，回滚
      EXEC [PE_THROW]           --//抛出错误

 END CATCH       
 ERR: 
       RAISERROR(@ERR,16, 1)  --//自定义错误，外部捕获
     
END 

假设PB的代码如下调用PA，并返回错误值

/*============================================================
-- 创建人:	Ylm	 
-- 创建日期: 
-- 功能描述:	

=============================================================*/
CREATE PROCEDURE PB (
  @P1 VARCHAR(30)='',
  @P2 VARCHAR(30)='',
  @OutPut VARCHAR(300)='' OUTPUT
	)
AS
BEGIN
 DECLARE @ERR VARCHAR(300)
    /*============================================================
     STEP 1:有效性检查
     ============================================================*/
     
    /*============================================================
     STEP 2:有效性检查
     ============================================================*/
     
    /*============================================================
     STEP 3:有效性检查
     ============================================================*/

 BEGIN TRY 
 BEGIN TRAN;
     /*============================================================
     STEP 1:执行更新
      ============================================================*/
     
     /*============================================================
     STEP 2:执行更新
      ============================================================*/
     DECLARE 
             @PRESULT INT=0,
             @NEWLZ_NO VARCHAR(30)
     EXEC	 @PRESULT = [dbo].[PA] @P_NO,
                            @CFrUsr,1,@NEWLZ_NO OUTPUT
     IF @PRESULT <>1
              GOTO ERR1
     /*============================================================
     STEP 3:执行更新
     ============================================================*/

 COMMIT TRAN;
 RETURN 1                     --//返回值
 ERR1: 
       RAISERROR(@ERR,16, 1)  --//存储过程错误，Catch捕获
 END TRY

 BEGIN CATCH
    IF @@TRANCOUNT>1          --//如果是嵌套的过程，必须COMMITTRAN,以减少当前计数,
       COMMIT   TRAN ;        --//一个存储过程只能有一个事物
     ELSE IF @@TRANCOUNT=1
       ROLLBACK TRAN;         --如果不是嵌套，回滚
     EXEC [PE_THROW]           --//抛出错误
   

 END CATCH       
 ERR: 
       RAISERROR(@ERR,16, 1)  --//自定义错误，外部捕获
      
END

说明：1.嵌套的存储过程使用两个标签，为什么使用两个标签呢

              ERR:标签作用于BEGIN TRY的外部，用于自定义错误的输出，如果是不是在嵌套的内部执行，其错误直接被客户断的应用程序捕获，如果是嵌套在内部执行，那么会被外部的BEGIN CATCH捕获。最终反馈到客户端

              ERR1:标签作用于BEGIN TRY的内部，用于针对存储过程执行的结果，来输出异常，并由CATCH捕获输出，如果不需要在BEGIN TRY 的内部执行异常抛出的话，这个ERR1:是可以被省略的，因为内部存储过程抛出的异常都会被BEGIN CATCH所捕获

            2.在CATCH内部使用了@@TRANCOUNT的判断

              1》IF@@TRANCOUNT>1COMMIT  TRAN ; 为什么@@TRANCOUNT>1的时候要执行COMMIT TRAN呢，是不是这样就会提交到数据库，永久保存呢，答案是否定的，先说@@TRANCOUNT的值是如何变化的，首先@@TRANCOUNT是针对一个连接所拥有的全局性变量，当使用一次BEGIN TRAN 则@@TRANCOUNT数值+1，当使用一次COMMIT TRAN时数值-1，如果在使用COMMIT TRAN 后的@@TRANCOUNT值为0，那么就会永久保存，如果不为0，表示还有待提交的事物。那么无论再何时，那一个存储过程调用ROLLBACK TRAN 都会将自第一个BGIN TRAN开始一来对数据的修改进行回滚，且将@@TRANCOUNT值0，所以COMMIT TRAN对数据的保存不是一定的，但对@@TRANCOUNT是一定修改的。当使用嵌套的存储过程时，不加以判断@@TRANCOUNT，而直接使用ROLLBACK TRAN 会造成外部的存储过程在调用ROLL BACKTRAN时抛出 EXECUE上一计数为0，下一计数为…不匹配的问题。在此时判断IF@@TRANCOUNT>1 COMMIT TRAN是将本次调用BEGIN TRAN的开销释放掉，并将计数回归到正确值，以便外部存储过程的事物能正确的提交或回滚；

              2》ELSEIF@@TRANCOUNT=1ROLLBACKTRAN;检查是否是嵌套内部的存储过程，如果不是，执行回滚。当然一个存储过程使用了几个BEGIN TRAN ,这个值要修改的

              3》EXEC[PE_THROW]是封装的一个抛出异常的过程，要放在前两条语句之后，放在之前的话，就有优先被外部的存储过程的CATCH捕获，那么肯定外部事物回滚的时候会报错的。这个PE_THROW过程摘至网上，网址没了，贴出来

Create PROCEDURE [dbo].[PE_THROW]

AS

BEGIN

    SET NOCOUNT ON;
     DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);
     SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
       SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();

    RAISERROR (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,
        @ErrorSeverity,
        @ErrorState,
        @ErrorProcedure,
        @ErrorLine
        );

END

 

SET XACT_ABORT ON 方法对于嵌套的存储过程抛出自定义的错误的被外部存储过程捕获的处理上，是存在一点问题的。

没了，最后一部分最好是把存储过程，存为SQL2008模版，使用的时候，点模版，直接再里面填内容最简单，不用管其他的。

码几个字，真M辛苦，算是自我的一个学习汇总把