Add Table Logging to Stored Procedure

1. Create table to store log data.
2. Create stored procedure to insert data into log table.
3. Add logging to user stored procedure.

dbo.tbMyLog.sql

CREATE TABLE dbo.tbMyLog
(
	[biLogId] [bigint] IDENTITY(1,1) NOT NULL,
	[tiLogLevel] [tinyint] NOT NULL,
	[vcParentSpName] [varchar](100) NOT NULL,
	[vcSpName] [varchar](100) NOT NULL,
	[dtCreateDateTime] [datetime2](0) NOT NULL,
	[vcMessage] [varchar](500) NULL,
	[iSqlSeverity] [int] NULL,
	[dtProcDateTime] [datetime2](0) NOT NULL,
	[vcInstance] [varchar](50) NULL,
	CONSTRAINT PK_tbTPLog PRIMARY KEY CLUSTERED 
	(
		biLogId ASC
	)
	WITH 
	(
		PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
	) ON fgData
) ON fgData;
GO

dbo.uspInsertMyLog.sql

CREATE PROCEDURE [dbo].[uspInsertMyLog]
(
	@vcParentSpName			VARCHAR(100),
	@vcSpName				VARCHAR(100),
	@vcMessage				VARCHAR(500),
	@tiLoggingLevel			TINYINT,		-- 1=fatal, 2=error, 3=warn, 4=info, 5=debug
	@tiConfigLoggingLevel	TINYINT = 2,	-- 1=fatal, 2=error, 3=warn, 4=info, 5=debug
	@dtProcDateTime			DATETIME2(0) = NULL,  -- Calling SP parent process starting date and time
	@iSqlSeverity			INTEGER = NULL,
	@vcInstance				VARCHAR(50) = NULL
)
AS
BEGIN

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	SET NOCOUNT ON;
	
	DECLARE @dtCurrentDateTime		DATETIME2(0) = SYSDATETIME();
	
	IF(@dtProcDateTime IS NULL)
	BEGIN
		SET @dtProcDateTime = @dtCurrentDateTime;
	END;

	IF(@tiConfigLoggingLevel >= @tiLoggingLevel)
	BEGIN
		INSERT INTO dbo.tbMyLog
		(
			tiLogLevel,
			vcParentSpName,
			vcSpName,
			dtCreateDateTime,
			vcMessage,
			dtProcDateTime,
			iSqlSeverity,
			vcInstance
		)
		VALUES
		(
			@tiLoggingLevel,
			@vcParentSpName,
			@vcSpName,
			@dtCurrentDateTime,
			@vcMessage,
			@dtProcDateTime,
			@iSqlSeverity,
			@vcInstance
		);
	END;
	RETURN 0;      -- OK
	
END

Logging in uspMySP01

DECLARE	
@vcSpName						VARCHAR(100) = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
@dtProcStartDateTime0			DATETIME2(0) = SYSDATETIME(),
@dtProcStartDateTime			DATETIME2(0) = SYSDATETIME(),
@tiCfgLoggingLevel				INTEGER = 5,
@vcMessage						VARCHAR(500) = NULL,
@iElapsedSeconds				INTEGER,
@vcElapsedSeconds				VARCHAR(8),
@vcElapsedTimeHHMMSS			VARCHAR(8),
@iSqlSeverity					INTEGER,	-- User set severity level
@vcInstance						VARCHAR(50) = NEWID(); 

-- Set the process start time as current system time with zero second
SET @dtProcStartDateTime = DATEADD(SECOND, -1 * DATEPART(SECOND, @dtProcStartDateTime), @dtProcStartDateTime);

EXEC TCS.dbo.uspMyLog @vcSpName, @vcSpName, 'Started', 4, @tiCfgLoggingLevel, @dtProcStartDateTime, NULL, @vcInstance;

SET @vcMessage = '@Param01: ' + CAST(@Param01 AS VARCHAR(50))
EXEC TCS.dbo.uspMyLog @vcSpName, @vcSpName, @vcMessage, 4, 
@tiCfgLoggingLevel, @dtProcStartDateTime, NULL, @vcInstance;

-- Do Something

	-- Get the process elapsed in seconds and HH:MM:SS string
	SET @iElapsedSeconds = DATEDIFF(SECOND, @dtProcStartDateTime0, SYSDATETIME());
	SET @vcElapsedSeconds = CAST(@iElapsedSeconds AS VARCHAR(8));
	SET @vcElapsedTimeHHMMSS = TCS.dbo.fnConvertSecondsToHHMMSS(@iElapsedSeconds);

   	SET @vcMessage = 'Total process time was ' + @vcElapsedTimeHHMMSS + ' (' + @vcElapsedSeconds + ' seconds)';
	EXEC TCS.dbo.uspMyLog @vcSpName, @vcSpName, @vcMessage, 4, @tiCfgLoggingLevel, @dtProcStartDateTime, NULL, @vcInstance;

	EXEC TCS.dbo.uspMyLog @vcSpName, @vcSpName, 'Completed', 4, @tiCfgLoggingLevel, @dtProcStartDateTime, NULL, @vcInstance;

SELECT @Err = @@ERROR
IF @Err<>0        
BEGIN
   RAISERROR('Error in procedure uspMySP01 SQL Error: %d',16,1, @Err) ;
   SET @ReturnValue = -1 ;

   		SELECT
			@vcMessage = 'SQL Error: ' + CAST(@Err AS VARCHAR(10)),
			@iSqlSeverity = ERROR_SEVERITY();
		SET @vcMessage = ISNULL(@vcMessage,  'No ERROR_MESSAGE available in CATCH section');
		EXEC TCS.dbo.uspMyLog @vcSpName, @vcSpName, @vcMessage, 2, @tiCfgLoggingLevel, @dtProcStartDateTime, @iSqlSeverity, @vcInstance;

   GOTO FINAL ;
END

FINAL:  

SELECT @ReturnValue ;   

GO

Remove log entries older than 1 month

DELETE FROM tbMyLog WHERE DATEDIFF(MONTH, dtCreateDateTime, SYSDATETIME()) > 1