CODEWITHSUNDEEP
sql-server
JivanK7
JivanK7

Reputation: 19

How to record stored procedure execution run time

Just looking for some help with a stored procedure in SQL Server. I have commonly come across the following code:

DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime=GETDATE() 

 -- Write Your Query


SELECT @EndTime=GETDATE()

--This will return execution time of your query
SELECT DATEDIFF(MS,@StartTime,@EndTime) AS [Duration in millisecs]

This only saves the data in the output panel(Messages). I was wondering if it was possible to save the results of the run time for the script each time into a table. For example a table that could look something like this:

CREATE TABLE Job_Batch(
    JobID NVARCHAR(MAX),
    JobDate NVARCHAR(MAX),
    JobTime NVARCHAR(MAX),
    JobDuration NVARCHAR(MAX),
    JobStatus NVARCHAR(MAX)
)

The script should also be able to generate its own JobID each time its run.

Upvotes: 1

Views: 2992

Answers (1)

pwilcox
pwilcox

Reputation: 5753

I'm changing your table up a bit just to capture things a little better:

declare @job_batch table (
    JobId int identity(1,1),
    JobName varchar(255),
    JobDate datetime,
    JobElapsedMs int,
    JobStatus nvarchar(4000)
);

Let me make a sample and temporary procedure to test the concept:

create or alter procedure #dummyProc as
begin
    declare @i int = 0;
    while @i < 100000 
    begin
        set @i += 1;
    end
end

This code below demonstrates putting the needed information into the logging table:

declare 
    @StartTime datetime = getdate(),
    @JobStatus nvarchar(4000);

begin try
    exec #dummyProc
    set @JobStatus = 'Success';
end try
begin catch
    set @JobStatus = error_message();
end catch

insert  @job_batch (JobName, JobDate, JobElapsedMs, JobStatus)
values  ('#dummyProc', @StartTime, datediff(ms,@StartTime,getdate()), @JobStatus);

When you query the @job_batch table:

select * from @job_batch;

You get this:

+-------+------------+-------------------------+--------------+------------+
| JobId |   JobName  |          JobDate        | JobElapsedMs | Job Status |
+-------+------------+-------------------------+--------------+------------+
|   1   | #dummyProc | 2021-01-28 00:22:57.370 |     60       |  Success   |
+-------+------------+-------------------------+--------------+------------+

Upvotes: 1

Related Questions