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]

/****** Object: StoredProcedure [dbo].[Sp_Tracker_Time_taken] Script Date: 11/25/2016 9:38:41 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Create procedure [dbo].[Sp_Tracker_Time_taken]

(

@sp_name varchar(100),

@param1 varchar(100),

@param2 varchar(100),

@param3 varchar(100),

@param4 varchar(100)

)

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

Total execution time for an SP

RECENT POST