对于在执行存储过程中碰到的一些错误,如果未及时捕获或者说传递给前端应用程序来,在这样的情形下,故障的排查显得尤为困难。基于此,我们可以可以将这些错误信息记录到日志,同时也可以将其传递给前端应用程序。如本文的示例。
一、将错误信息记录到表
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 CatchErrorDemoAS --相关业务逻辑 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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
二、传递错误信息
CREATE PROC CatchErrorDemo2 @a INT , @rtn VARCHAR(20) OUTPUT , @rtn_message VARCHAR(200) OUTPUTAS 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;245Error Number: 245 Error Serverity: 16 Error State: 1 Error Procedure: CatchErrorDemo2 Error Line: 10 Error Message: 在将 varchar 值 'A' 转换成数据类型 int 时失败。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow