Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment