/*
LOG STORED PROCEDURE RUN TIMES AND ERROR MESSAGES

Example:
====================
-- Place at beginning of your stored procedure
DECLARE @Parameters VARCHAR(200) = NULL;
SET @Parameters = 'Account: ' + ISNULL(@Account,'') + ' UserID: ' + ISNULL(@UserID,'');
DECLARE @StartTime DATETIME = GETDATE();
EXEC Call_ssProcedureLog @ObjectID = @@PROCID, @StartDate = @StartTime, @AdditionalInfo = @Parameters;

-- Place at end of your stored procedure
DECLARE @EndDate DATETIME  = GETDATE();
EXEC Call_ssProcedureLog @ObjectID = @@PROCID, @StartDate = @StartTime, @EndDate = @EndDate;
*/

IF OBJECT_ID('[dbo].[sp_log]') IS NULL
BEGIN
    CREATE TABLE [dbo].[sp_log](
	    [ID] [int] IDENTITY(1,1) NOT NULL,
	    [StartDate] [datetime] NULL,
	    [EndDate] [datetime] NULL,
	    [DatabaseID] [int] NULL,
	    [ObjectID] [int] NULL,
	    [ProcedureName] [nvarchar](100) NULL,
	    [RunTimeSec]  AS (case when [EndDate] IS NOT NULL AND [StartDate] IS NOT NULL then datediff(second,[StartDate],[EndDate])  end),
	    [ErrorLine] [int] NULL,
	    [ErrorMessage] [nvarchar](4000) NULL,
	    [AdditionalInfo] [nvarchar](4000) NULL,
	CONSTRAINT [PK_sp_log] PRIMARY KEY CLUSTERED
    (
	    [ID] ASC
    )WITH (FILLFACTOR = 97) ON [PRIMARY]
    ) ON [PRIMARY]
END
GO

IF OBJECT_ID('[dbo].[sp_logger]') IS NOT NULL DROP PROCEDURE [dbo].[sp_logger]
GO

CREATE PROCEDURE [dbo].[sp_logger]
 @StartDate	  DATETIME = NULL,
 @EndDate		  DATETIME = NULL,
 @ObjectID       INT = NULL,
 @DatabaseID     INT = NULL,
 @RunTimeSec	  INT = NULL,
 @AdditionalInfo NVARCHAR(4000) = NULL
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE
  @ProcedureName NVARCHAR(100);

 SET @DatabaseID = COALESCE(@DatabaseID, DB_ID());
 SET @ProcedureName = COALESCE
  (
   QUOTENAME(DB_NAME(@DatabaseID)) + '.'
   + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID))
   + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)),
   ERROR_PROCEDURE()
  );

  IF @EndDate IS NOT NULL
  BEGIN
    UPDATE sp_log
      SET EndDate = @EndDate
      where StartDate = @StartDate
      AND ObjectID = @ObjectID
  END
  ELSE
  BEGIN
	INSERT sp_log
	(
	 StartDate,
	 EndDate,
	 DatabaseID,
	 ObjectID,
	 ProcedureName,
	 ErrorLine,
	 ErrorMessage,
	 AdditionalInfo
	)
	SELECT
	 @StartDate,
	 @EndDate,
	 @DatabaseID,
	 @ObjectID,
	 @ProcedureName,
	 ERROR_LINE(),
	 ERROR_MESSAGE(),
	 @AdditionalInfo;
  END
END
GO