SQLserver 存储过程执行错误记录到表

系统管理员 2022-08-21 15:58 24阅读 0赞

> 对于在执行存储过程中碰到的一些错误，如果未及时捕获或者说传递给前端应用程序来，在这样的情形下，故障的排查显得尤为困难。基于此，我们可以可以将这些错误信息记录到日志，同时也可以将其传递给前端应用程序。如本文的示例。

## 一、将错误信息记录到表 ##

CREATE TABLE ErrorLog ( ID INT IDENTITY (1,1) NOT NULL, ErrorNumber INT, ErrorMessage VARCHAR(1000), ErrorSeverity INT, ErrorState INT, ErrorLine INT, ErrorProcedure VARCHAR(128), ErrorDate datetime );
    
    CREATE PROCEDURE CatchErrorDemo AS --相关业务逻辑 BEGIN TRY SELECT 1 / 0;
        END TRY --相关错误捕获 BEGIN CATCH PRINT 'Error Number:' + CAST(ERROR_NUMBER() AS VARCHAR(10));
            PRINT 'Error Serverity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
            PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10));
            PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
            PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
            PRINT 'Error Message: ' + ERROR_MESSAGE();
            INSERT INTO ErrorLog ( ErrorNumber , ErrorSeverity , ErrorState , ErrorProcedure , ErrorLine , ErrorMessage , ErrorDate ) VALUES ( ERROR_NUMBER() , ERROR_SEVERITY() , ERROR_STATE() , ERROR_PROCEDURE() , ERROR_LINE() , ERROR_MESSAGE() , GETDATE() ) END CATCH;
    
    --执行相关存储过程，并查询日志表，同时与系统消息表进行对比
    EXEC CatchErrorDemo;
    SELECT * FROM ErrorLog;

![这里写图片描述][20160506181103857]

## 二、传递错误信息 ##

CREATE PROC CatchErrorDemo2 @a INT , @rtn VARCHAR(20) OUTPUT , @rtn_message VARCHAR(200) OUTPUT AS BEGIN BEGIN TRY DECLARE @result INT; 
    
                SET @result = 'A' + @a;
            END TRY BEGIN CATCH SET @rtn = @@ERROR;
    
                SET @rtn_message = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' Error Serverity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + ' Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + ' Error Procedure: ' + ERROR_PROCEDURE() + ' Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ' Error Message: ' + ERROR_MESSAGE();
            END CATCH;
        END;
    GO
    
    --调用存储过程
    DECLARE @rtn INT ,
        @rtn_message VARCHAR(200);
    EXEC CatchErrorDemo2 0, @rtn OUTPUT, @rtn_message OUTPUT;
    PRINT @rtn;
    PRINT @rtn_message;
    
    245
    Error Number: 245 Error Serverity: 16 Error State: 1 Error Procedure: CatchErrorDemo2 Error Line: 10 
    Error Message: 在将 varchar 值 'A' 转换成数据类型 int 时失败。

[20160506181103857]: /images/20220724/c9147ec51c74458f8bbb5cf915be24c2.png