Thursday 24 November 2016

Total execution time for an SP

/*Keep a record to take the total execution time for SP*/
I am dynamically passing the values like SP name and parameter are using for that SP.

USE [YOUR_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[Sp_Tracker_Time_taken]    Script Date: 11/25/2016 9:38:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[Sp_Tracker_Time_taken]
(
@sp_name varchar(100),
@param1 varchar(100),
@param2 varchar(100),
@param3 varchar(100),
@param4 varchar(100)
)
as
begin

declare @error_number int
declare @error_message varchar(100)
declare @error_stat as tinyint

declare @startproc datetime
declare @endproc datetime
declare @time varchar(30)
declare @time1 varchar(30)
declare @nulls varchar(100) =0

begin try

SELECT sp_s = name into #sp_list
FROM sys.procedures


declare @spName varchar(50)
set @spName= (select sp_s from #sp_list where sp_s like  @sp_name )
select @startproc = getdate()

--============================
--============================
if(@param2=@nulls)
exec @sp_name @param1

else if(@param3 = @nulls)
exec @sp_name @param1,@param2

else if(@param4 = @nulls)
exec @sp_name @param1,@param2,@param3

--else
--exec @sp_name @param1,@param2,@param3,@param4
--================================

select @endproc = getdate()
select @time = DATEDIFF(SECOND, @startproc, @endproc)

select @time1=RIGHT('0' + CAST(@time / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@time / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(@time % 60 AS VARCHAR),2)


insert into Tracker_Query(Sp_Name,Sys_No,Sp_St_Time,Sp_Fin_Time,Tot_Time_Exec)
values(@spName,host_name(),@startproc,@endproc,@time1+ ' ' +'Mints')

end try
begin catch
select @error_number=ERROR_NUMBER()
select @error_message=ERROR_MESSAGE()
select @error_stat=ERROR_STATE()
RAISERROR (@error_number, @error_message, @error_stat)
end catch

end


No comments:

Post a Comment