SQLTeam.com | Weblogs | Forums

Count calls to a function


#1

I want to add a row to a table each time my function executes. SQL doesn't allow functions to write data directly so I'm looking for an alternative method.

-- My Function looks like this...
CREATE FUNCTION Foo
( @param1 int)
RETURNS TABLE
AS
RETURN
(
with MyValues as
( select 1 as val union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
)

select * from MyValues where val > @param1
)
GO

My table looks like this...
CREATE TABLE [dbo].[CallsToFooFunction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StartTime] [datetime] NULL, -- time function execution started
[Endtime] [date] NULL -- time function execution ended
) ON [PRIMARY]

Thanks in advance!


#2

Normally you can't do inserts in a function (See here).
Use should consider changing your function to stored procedure or view (with option to create trigger).
I know everybody will scold me for this, but you actually can do your insert from inside your function. I do NOT recomend this, but just to show, it can be done, here goes:

create function Foo(@param1 int)
returns @tmptable table (val int)
as
begin
   declare @dts datetime=getdate()
          ,@dte datetime
          ,@sql varchar(8000)
          ,@cmd varchar(4000)
          ;
   with MyValues(val)
     as (          select 1
         union all select 2
         union all select 3
         union all select 4
         union all select 5
         union all select 6
        )
   insert into @tmptable(val)
   select val from MyValues where val>@param1;
   set @dte=getdate();
   set @sql='insert into dbo.CallsToFooFunction(StartTime,EndTime)'
           +' values('''+convert(varchar(23),@dts,126)+''','''+convert(varchar(23),@dte,126)+''')'
           ;
   select @cmd='sqlcmd -S '+@@servername+' -d '+db_name()+' -Q "'+@sql+'"';
   exec xp_cmdshell @cmd,'no_output';
   return;
end

Oh, and also it makes more sence that the EndTime field be a datetime field.
Now everyone - START SCOLDING ME :laughing:


#3

Hey thanks.. my request to do this is part of a performance analysis - and techniques like you describe are temporary measures - soley to understand when a specific function is called.

In our real application we are converting a particular function to SP - but first we want to understand how often its being called and the call origin.

Will try your solution. Very much appreciated!!

John