1 CREATE TABLE ErrorLog(
2 errNum INT,
3 ErrSev NVARCHAR(1000),
4 ErrState INT,
5 ErrProc NVARCHAR(1000),
6 ErrLine INT,
7 ErrMsg NVARCHAR(2000)
8 )
9
10 CREATE PROCEDURE ProcErrorLog
11 AS
12 BEGIN
13 SELECT
14 ERROR_NUMBER() AS ErrNum,--返回导致运行 CATCH 块的错误消息的错误号。
15 ERROR_SEVERITY()AS ErrSev,--返回导致 CATCH 块运行的错误消息的严重级别
16 ERROR_STATE() AS ErrState,--返回导致 CATCH 块运行的错误消息的状态号
17 ERROR_PROCEDURE() AS ErrProc,--返回出现错误的存储过程名称
18 ERROR_LINE()AS ErrLine,--返回发生错误的行号
19 ERROR_MESSAGE()AS ErrMsg--返回导致 CATCH 块运行的错误消息的完整文本
20 INSERT INTO ErrorLog VALUES(
21 ERROR_NUMBER(),
22 ERROR_SEVERITY(),
23 ERROR_STATE(),
24 ERROR_PROCEDURE(),
25 ERROR_LINE(),
26 ERROR_MESSAGE())
27 END
28
29 CREATE PROCEDURE TestErrorLog
30 AS
31 BEGIN
32 BEGIN TRY
33 SELECT GETDATE()
34 SELECT 1/0--Evergreen divide by zero example!
35 END TRY
36 BEGIN CATCH
37 SELECT 'There was an error! ' + ERROR_MESSAGE()
38 ProcErrorLog --调用上面的存储过程,保存错误日志
39 RETURN
40 END CATCH;
41 END
