Hello everyone
I have a 16 period fields in a schema, for example PreviousMonth where each date in the previous month has a value of 1
I trying to write a function that returns the Max date key dependant on the Field I pass in PreviousMonth, NextMothh etc
I have the code below, where the SQL string is dynamic. I have read that you cannot have dynamic SQL in a UDF,
What’s the best way to return the Max date of each period, as I’m trying to write a query to determine if the Max date is between two other date fields.
I also tried a stored procedure, but again I read you cannot include a stored procedure in a query…
Alter FUNCTION dbo.ufnGetMaxPeriodDate(@Period Varchar(50))
RETURNS int
AS
BEGIN
DECLARE @ret int;
DECLARE @SQL nvarchar(Max)
set @SQL=' SELECT MAX(DateKey) AS MaxDate
FROM dbo.DimDate d
WHERE d.'+@Period+'=1 '
EXECUTE SP_EXECUTESQL @SQL;
Set @ret=@SQL
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
DECLARE @returnvalue int;
EXEC @returnvalue = dbo.ufnGetMaxPeriodDate('PreviousMonth')
PRINT @returnvalue
Thanks in Advance
Ron