/
Timings.sql
144 lines (115 loc) · 3.45 KB
/
Timings.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
-- Set up Tables/Procedures for Timing Audits
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Timings')
BEGIN
CREATE TABLE [dbo].[Timings]
(
[Code] NVARCHAR(10) NOT NULL,
[Description] NVARCHAR(100),
[ActionTime] DATETIME NOT NULL,
[IsComplete] BIT DEFAULT(0)
CONSTRAINT [PK_Timings] PRIMARY KEY CLUSTERED
(
[Code] ASC,
[IsComplete] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'up_RecordStart')
BEGIN
DROP PROCEDURE [dbo].[up_RecordStart]
END
GO
CREATE PROCEDURE [dbo].[up_RecordStart]
( @Code NVARCHAR(10),
@Description NVARCHAR(100) = '')
-- @Code - Parameter used to uniquely identify this action
-- @Description - Parameter used to record a description for this action
-- ActionTime is auto-populated with GETDATE()
-- IsComplete is auto-populated with 0 to indicate this is not a completion
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
INSERT INTO [dbo].[Timings]
( [Code], [Description], [ActionTime], [IsComplete])
VALUES
( @Code, @Description, GETDATE(), 0)
COMMIT TRAN
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
PRINT 'ERROR: You hve already used the code ' + @Code + ' for timing purposes. Please choose a different code and try again.'
ELSE
PRINT 'ERROR: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' - ' + ERROR_MESSAGE()
ROLLBACK TRAN
END CATCH
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'up_RecordEnd')
BEGIN
DROP PROCEDURE [dbo].[up_RecordEnd]
END
GO
CREATE PROCEDURE [dbo].[up_RecordEnd]
( @Code NVARCHAR(10))
-- @Code - Parameter used to uniquely identify this action
-- @Description is only populated at the start point, not at end point
-- ActionTime is auto-populated with GETDATE()
-- IsComplete is auto-populated with 1 to indicate this is completion
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
INSERT INTO [dbo].[Timings]
( [Code], [ActionTime], [IsComplete])
VALUES
( @Code, GETDATE(), 1)
COMMIT TRAN
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
PRINT 'ERROR: You hve already used the code ' + @Code + ' for timing purposes. Please choose a different code and try again.'
ELSE
PRINT 'ERROR: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' - ' + ERROR_MESSAGE()
ROLLBACK TRAN
END CATCH
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'up_GetTimings')
BEGIN
DROP PROCEDURE [dbo].[up_GetTimings]
END
GO
CREATE PROCEDURE [dbo].[up_GetTimings]
( @Code NVARCHAR(10) )
-- @Code - Parameter used to identify this action uniquely
-- Returns Code, Description, and Length of time in ms between the start and end actions
-- If no start or end action is found then TimeInMS will be -1
-- Description is taken from the Start Action
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @StartTime AS DATETIME
DECLARE @EndTime AS DATETIME
DECLARE @Length AS INT
SET @Length = 0
SELECT @StartTime = ActionTime
FROM Timings
WHERE Code = @Code
AND IsComplete = 0
SELECT @EndTime = ActionTime
FROM Timings
WHERE Code = @Code
AND IsComplete = 1
IF ISNULL(CAST(@StartTime AS VARCHAR(12)),'NULL') = 'NULL'
SET @Length = -1
IF ISNULL(CAST(@EndTime AS VARCHAR(12)),'NULL') = 'NULL'
SET @Length = -1
IF @Length >= 0
SET @Length = DATEDIFF(ms,@StartTime,@EndTime)
SELECT Code, Description, @Length AS TimeInMS
FROM [dbo].[Timings]
WHERE [Code] = @Code
AND IsComplete = 0
GO