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!
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
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