[RESOLVED] Difference

[kaffenils]

Could you show a sample please of how you start the UDF off, so that we can see how you indicate a return value of a TABLE?

This is a function we use to calculate daily work load based on a job system we have by transforming hours assigned between a start and end date into hours per day. I have removed the actual T-SQL code since that would be meaningless to include.

CREATE function dbo.fn_CalculateResourceWorkLoad (@ContactId int, @StartEndDateByMonth bit=0) 
returns @calendar table([date] datetime primary key,
hours_assigned float default 0,
hours_total float default 0,
no_work_day bit default 0)
 
as 
begin 
 
-- Execute TSQL here to populate the @calendar table.
 
return 
end

We then use the function in a SELECT statement to calculate work load grouped by month, week, year, quarter etc.

set datefirst 1 
 
select year([date]) as year,datepart(ww,[date]) as week,
round(sum(hours_assigned),1) as hours_assigned,
sum(hours_total) as hours_total,
min([date]) as start_date,
max([date]) as end_date
from dbo.fn_CalculateResourceWorkLoad(@ContactId,0)
group by year([date]),datepart(ww,[date])

You could have done that same by only using stored procedures, but I thought that using the UDF as a recordset was so elegant that I just had to do it.