Stored Procedure Error Logging
Create table to capture Errors
Create Stored Procedure to Log Error
Invoke Stored Procedure in TRY/CATCH Block
|
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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
USE TEMPDB; GO IF OBJECT_ID('APM.ErrorLog') IS NOT NULL DROP TABLE APM.ErrorLog; GO CREATE TABLE APM.ErrorLog ( ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY, DatabaseID INT, DatabaseName sysname, SPID INT, ErrorNumber INT, ErrorSeverity INT, ErrorState INT, ErrorProcedure VARCHAR(400), ErrorMessage NVARCHAR(MAX), AdditionalInfo NVARCHAR(MAX), CreatedBY VARCHAR(50) DEFAULT SYSTEM_USER, CreatedDateTime DATETIME DEFAULT GETUTCDATE(), Status INT DEFAULT 1 ); GO CREATE OR ALTER PROC APM.LogErrorInfo ( @pAdditionalIndfo NVARCHAR(550) = NULL, @pCreatedBy VARCHAR(50) = NULL ) AS BEGIN INSERT INTO APM.ErrorLog ( DatabaseID, DatabaseName, SPID, ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorMessage, AdditionalInfo, CreatedBY ) SELECT DB_ID(), -- DatabaseID - int DB_NAME(), -- DatabaseName - sysname @@SPID, -- SPID - int ERROR_LINE(), -- ErrorNumber - int ERROR_SEVERITY(), -- ErrorSeverity - int ERROR_STATE(), -- ErrorState - int ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400) ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max) ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max) ISNULL(@pCreatedBy, SYSTEM_USER); SELECT DB_ID(), -- DatabaseID - int DB_NAME(), -- DatabaseName - sysname @@SPID, -- SPID - int ERROR_LINE(), -- ErrorNumber - int ERROR_SEVERITY(), -- ErrorSeverity - int ERROR_STATE(), -- ErrorState - int ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400) ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max) ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max) ISNULL(@pCreatedBy, SYSTEM_USER); /* -- UNIT TEST BEGIN TRY SELECT 1 /0 END TRY BEGIN CATCH EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test' END CATCH SELECT * FROM APM.ErrorLog */ END; |
Loading...
Leave a Reply