对于在执行存储过程当中碰到的一些错误，若是未及时捕获或者说传递给前端应用程序来，在这样的情形下，故障的排查显得尤其困难。基于此，咱们能够能够将这些错误信息记录到日志，同时也能够将其传递给前端应用程序。如本文的示例。前端
1.将错误信息记录到表
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;
2.传递错误信息
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 时失败。
