SQLServer异常捕获(Try Catch)与回滚

 

SQLServer异常捕获(Try Catch)与回滚

 

/*
SQLServer异常捕获
 
在SQLserver数据库中，如果有很多存储过程的时候，我们会使用动态SQL进行存储过程调用存储过程，这时候，很可能在某个环节就出错了，但是出错了我们很难去跟踪到出错的存储过程，此时我们就可以使用异常处理，通过日志表来记录存储过程的执行情况，然而定位到错误的存储过程，以下是一个存储过程异常处理的小实例：
 
*/
 
/*=====================================================
相关错误消 息如下：
 
ERROR_NUMBER() 返回错误号。
 
ERROR_SEVERITY() 返回严重性。
 
ERROR_STATE() 返回错误状态号。
 
ERROR_PROCEDURE() 返回出现错误的存储过程或 触发器的名称。
 
ERROR_LINE() 返回导致错误的例程中的行 号。
 
ERROR_MESSAGE() 返回错误消息的完整文本。
========================================================*/
 
CREATE TABLE #LogTable  /*可以建一个实体表来保存错误的信息*/
(
  ID              int identity(1,1),--错误序号
  ErrorNumber     int,--错误号
  ErrorSeverity   int,--严重性
  ErrorState      int,--错误状态号
  ErrorProducure  varchar(200),--出现错误的存储过程或 触发器的名称
  ErrorLine       int,--导致错误的例程中的行号
  ErrorMessage    varchar(200)--错误消息的完整文本
)
 
--===============除数不为0的异常捕获=================--
IF EXISTS ( SELECT  *
            FROM    sysobjects
            WHERE   id = OBJECT_ID(N'TestTryCatch')
                    AND xtype = 'P' )
    DROP PROC TestTryCatch;
GO
 
CREATE TABLE #Department
(
    DeptID VARCHAR(20) PRIMARY KEY,
    DeptName NVARCHAR(50)
)
GO
 
DELETE FROM #Department
 
CREATE PROC TestTryCatch
AS
BEGIN
    BEGIN TRY                   --开始捕捉异常
        BEGIN TRAN;             --开始事务
 
        INSERT INTO #Department ( DeptID, DeptName )   
        VALUES  ( 'D0001', N'人力资源TEAM')          
        /*
             不加事务， 每一句就是一个事务，
             数据插入#Department中,之后不会再回滚。     
        */
 
        PRINT 'Before Error'       
 
        SELECT  1 / 0;          -- 业务处理段   
         
        PRINT 'After Error'    
 
        /* 无论加不加事务， 错误之后的语句都不再执行 */
        INSERT INTO #Department ( DeptID, DeptName )   
        VALUES  ( 'D0002', N'财务TEAM')  
         
        COMMIT TRAN;            --提交事务
    END TRY                     --结束捕捉异常
    BEGIN CATCH                 --有异常被捕获
        IF @@TRANCOUNT > 0       --判断有没有事务
            BEGIN
                ROLLBACK TRAN;  --回滚事务
            END;
     
        DECLARE @ErrorMsg NVARCHAR(MAX);
        SELECT  @ErrorMsg = ERROR_MESSAGE();
        RAISERROR(@ErrorMsg,16,1);
 
        INSERT  INTO #LogTable
        VALUES  ( ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() );
 
    END CATCH;                  --结束异常处理
END
 
--执行存储过程
EXEC TestTryCatch;
 
--查看日志表
SELECT  *
FROM    #LogTable;
 
--查看系统日志表
SELECT  *
FROM    sys.messages
WHERE   message_id = 8134
        AND language_id = 2052;
 
--查看系统的messages表
SELECT  *
FROM    sys.messages
WHERE   language_id = 2052
ORDER BY message_id;