Universal Error Logging for Stored Procedures in SQL Server

Universal Error Logging for Stored Procedures in SQL Server

-- CHANGE [DatabaseName] TO THE NAME OF THE DB REQUIRED
-- CHANGE SP_NAME TO THE NAME OF THE SP REQUIRED
---------------------------------------------------
-- STEP 1
-- CREATE TABLE TO STORE ERRORS
---------------------------------------------------
USE [DatabaseName]
GO

-- DROP LOG TABLE IF ALREADY EXISTS
IF OBJECT_ID('dbo.SP_Log', 'U') IS NOT NULL
	DROP TABLE dbo.SP_Log
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

-- CREATE LOG TABLE
CREATE TABLE [dbo].[SP_Log] (
	[LogID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL
	,[NameOfSp] [nvarchar](100) NOT NULL
	,[StepCode] [varchar](1) NOT NULL
	,[RunDate] [datetime] NOT NULL
	,[Comment] [nvarchar](200) NULL
	,PRIMARY KEY CLUSTERED ([LogID] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

---------------------------------------------------
-- STEP 2
-- CREATE STORED PROCEDURE TO WRITE ERRORS TO ERROR TABLE
---------------------------------------------------
USE [DatabaseName]
GO

IF OBJECT_ID('dbo.InsertRunIntoSpLog', 'p') IS NOT NULL
	DROP PROCEDURE [dbo].[InsertRunIntoSpLog]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- CREATE STORED PROCEDURE
CREATE PROCEDURE [dbo].[InsertRunIntoSpLog] @SpVariableName NVARCHAR(100)
	,@StepCode VARCHAR(1)
	,@Comment NVARCHAR(400)
AS
BEGIN
	INSERT INTO SP_Log (
		NameOfSp
		,StepCode
		,RunDate
		,Comment
		)
	VALUES (
		@SpVariableName
		,@StepCode
		,getutcdate()
		,@Comment
		)
END
GO

---------------------------------------------------
-- STEP 3
-- STORED PROCEDURE RUN / ERROR CATCH EXAMPLE
---------------------------------------------------
IF OBJECT_ID('dbo.SP_Name', 'p') IS NOT NULL
	DROP PROCEDURE [dbo].[SP_Name]
GO

CREATE PROCEDURE [dbo].[SP_Name]
AS
BEGIN
	BEGIN TRY
		-- INSERT SP RUN LOG FOR SP START
		EXEC InsertRunIntoSpLog 'SP_NAME' -- SP_NAME
			,'S' -- S, F OR E (START, FINNISH OR ERROR)
			,'' -- USER COMMENT

		-- QUERY HERE 
		-- FOR EXAMPLE
		SELECT GETDATE() AS CurrentDateTime

		-- INSERT SP RUN LOG FOR SP END
		EXEC InsertRunIntoSpLog 'SP_NAME'
			,'F'
			,''
	END TRY

	BEGIN CATCH
		PRINT 'error!'

		-- INSERT SP RUN LOG FOR SP ERROR
		DECLARE @error_number AS INTEGER
		DECLARE @error_message AS VARCHAR(400)

		SET @error_number = error_number()
		SET @error_message = left(error_message(), 400)

		PRINT 'error_message: ' + @error_message

		EXEC InsertRunIntoSpLog 'SP_NAME'
			,'E'
			,@error_message
	END CATCH
END