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 @diffMs bigint
declare @hour int
declare @min int
declare @sec int
declare @ms int
declare @hourDisplay varchar(2)
declare @minDisplay varchar(2)
declare @secDisplay varchar(2)
declare @msDisplay varchar(3)

set @diffMs = DateDiff(MILLISECOND, @start, @end)
set @diff = @diffMs / 1000
set @hour = @diff / 3600
set @min = @diff / 60
set @min = @min % 60
set @sec = @diff % 60
set @ms = @diffMs - (@diff * 1000)

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
    set @msDisplay = Convert(varchar, @ms)
    if(@ms < 10)
        set @msDisplay = @msDisplay + '00'
    if(@ms < 100)
        set @msDisplay = @msDisplay + '0'

    return @msg + char(9) + @hourDisplay + ':' + @minDisplay + ':' + @secDisplay + '.' + @msDisplay
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.