Home > SQL Server > SQL Server Print logging message function

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

print dbo.udfGetDurationMsg('Create Index xyz', @start, @end)

Here is a second version this puts tabs in and formats as time so you can copy and paste into excel šŸ™‚
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

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: