Procedure Error Logging method

Imagine you run 1000's of stored procedure every day in your organization through various applications. How do we keep an eye on them?

To do this, lets create a table where we log procedure causing failure.

--Create Error Log Table to save Error Information

CREATE TABLE [dbo].[ProcedureErrorLog](
LogDate     SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
DatabaseID INT,
DatabaseName varchar(100),
ProcedureName VARCHAR(400),
ErrorNumber [int] NOT NULL,
ErrorMessage [varchar](4000) COLLATE Latin1_General_BIN NULL,
ErrorSeverity [smallint] NOT NULL,
ErrorState [smallint] NOT NULL DEFAULT ((1)),
ErrorLine [int] NOT NULL DEFAULT ((0)),
UserName [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
HostName [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
TimeStamp datetime NOT NULL,
)
GO

CREATE CLUSTERED INDEX LogDate ON ProcedureErrorLog(LogDate);

GO

Go ahead and create a stored procedure which store all the information in our log table. You can customized to include more information about this event.


CREATE procedure dbo.usp_GLC_StoredProcedureErrorLog AS
-- Declaration statements
DECLARE @LogDate DateTime
DECLARE @DatabaseID    INT
DECLARE @DatabaseName varchar(100)
DECLARE @ObjectID    INT
DECLARE @ProcedureName  VARCHAR(400)
DECLARE @ErrorNumber int
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(200)
DECLARE @ErrorLine int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @TimeStamp datetime

-- Initialize variables
SELECT @LogDate = GETDATE(),
@DatabaseID = COALESCE(@DatabaseID, DB_ID()),
@DatabaseName = DB_NAME(),
@ProcedureName = COALESCE
  (
   QUOTENAME(DB_NAME(@DatabaseID)) + '.'
   + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID))
   + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)),
   ERROR_PROCEDURE()
  ),
@ErrorNumber = isnull(error_number(),0),
@ErrorMessage = isnull(error_message(),'NULL Message'),
@ErrorSeverity = isnull(error_severity(),0),
@ErrorState = isnull(error_state(),1),
@ErrorLine = isnull(error_line(), 0),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@TimeStamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ProcedureErrorLog (LogDate, DatabaseID,DatabaseName, ProcedureName, ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, UserName, HostName, [TimeStamp])

SELECT @LogDate, @DatabaseID, @DatabaseName, @ProcedureName, @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,@UserName, @HostName, @TimeStamp


---Test this procedure



CREATE PROCEDURE dbo.spTest AS
BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

EXEC dbo.usp_GLC_StoredProcedureErrorLog

END CATCH

Exec dbo.spTest

--
SELECT * FROM ProcedureErrorLog