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