Tag Archives: sql server

Universal Error Logging for Stored Procedures in SQL Server

Reporting, SQL Server, T-SQL, , , , , , , ,

Probably the best way to log whether your stored procedure ran correctly or threw an error during its run is to create another stored procedure to capture the events of the run.

This example although basic captures the primary data you would want to keep a record of regarding a procedures run.

  • The name of the stored procedure
  • The start time of the stored procedure
  • The time the stored procedure finished/errored
  • A user comment/error message
  • An assigned Log ID

Below is a pre-baked example requiring just a change to the DatabaseName, and if you wish SP_Name, text fields, as instructed in the comments of the SQL below, to test run the script.

This will create a SP_Log table and the stored procedure InsertRunIntoSpLog which can be reused over and over again when making stored procedures for other jobs to log the success/failure of these jobs.

Step 3 is an example stored procedure, which can be renamed as something other than SP_Name, which simply displays the date time.  Once created whenever the following is executed, EXEC dbo.SP_Name, the current date time will be returned and a record of the procedures run will be added to a row in the SP_Log table.

Although as stated above this is a basic example it is a very powerful foundation which can be built upon for very comprehensive event and transaction logging.

 


— 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

view raw

Universal Error Logging for Stored Procedures in SQL Server

hosted with ❤ by GitHub