SQL Server Print logging message function

I use this to log duration information when I am testing stuff with Sql

create function [dbo].[udfGetDurationMsg]
(@msg varchar(max), @start datetime, @end datetime) RETURNS VARCHAR(100)
AS
BEGIN
	declare @diff bigint
	set @diff = DateDiff(ss, @start, @end)
	return @msg + ' - ' + Convert(varchar, @diff / 60) + 'mins ' + Convert(varchar, @diff % 60) + 'sec'
End

I find this useful when testing how loing things are taking in big procedures

You call it like this

CREATE function [dbo].[udfGetDurationMsg](@msg varchar(max), @start datetime, @end datetime) RETURNS VARCHAR(100)
AS
BEGIN
	declare @diff bigint
	declare @hour int
	declare @min int
	declare @sec int
	declare @hourDisplay varchar(2)
	declare @minDisplay varchar(2)
	declare @secDisplay varchar(2)
	set @diff = DateDiff(ss, @start, @end)
        set @hour = @diff / 3600
	set @min = @diff / 60
	set @min = @min % 60
	set @sec = @diff % 60
	set @hourDisplay = Convert(varchar, @hour)
	if(@hour < 10)
		set @hourDisplay = '0' + @hourDisplay
	set @minDisplay = Convert(varchar, @min)
	if(@min < 10)
		set @minDisplay = '0' + @minDisplay
	set @secDisplay = Convert(varchar, @sec)
	if(@sec < 10)
		set @secDisplay = '0' + @secDisplay
	return @msg + char(9) + @hourDisplay + ':' + @minDisplay + ':' + @secDisplay
End

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.