Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement