Reputation: 275
I have this function that given a initial and final date gives the corresponding year/month in that range:
CREATE FUNCTION [dbo].[fnYearMonth]
(
@Initial Date,
@Final Date
)
RETURNS TABLE
AS
RETURN
With dateRange(StatDate) as
(
select @Initial
union all
select dateadd(month, 1, StatDate)
from dateRange
where dateadd(month, 1, StatDate) <= CAST(DATEADD(month,DATEDIFF(month,0,@Final)+1,0)-1 as Date)
)
select DATEPART(year, StatDate) AS MyYear, DATEPART(month, StatDate) AS MyMonth From dateRange where StatDate <= @Final
The problem is that the default limit of MAXRECURSION of 100 only makes available date ranges of a maximum of 8 years and 4 months. That is insufficient.
I tried using "OPTION (MAXRECURSION 2000);" in the functions that use this function but that didn't work because I called this function in a WITH statement.
My only solution now is to turn this inline function into a multi-statement function and use "OPTION (MAXRECURSION 2000);". But I would prefer to avoid this option for performance reasons. ¿Is any other alternative?
Thanks for the help.
Upvotes: 4
Views: 1713
Reputation: 28860
Try adding OPTION (MAXRECURSION 0)
or recursion limit you wish at bottom like below..
You also can use a Calendar table to avoid all these calculations which gives the output you need..
I have a calendar table populated in my database,the output is so easy to calculate like below..I recommend having a table instead of repeated calculations
select distinct month,year from dbo.calendar
where dAte>=getdate()-200 and date<=getdate()
If you wish to go with recursive option ,add option(recursion)
like below
--this wont work with inline table valued functions,see below demo Alter FUNCTION [dbo].[fnYearMonth] ( @Initial Datetime, @Final Datetime ) RETURNS TABLE AS RETURN With dateRange as ( select @Initial as statdate union all select dateadd(month, 1, StatDate) from dateRange where dateadd(month, 1, StatDate) <= CAST(DATEADD(month,DATEDIFF(month,0,@Final)+1,0)-1 as Datetime) ) select DATEPART(year, StatDate) AS MyYear, DATEPART(month, StatDate) AS MyMonth From dateRange where StatDate <= @Final OPTION (MAXRECURSION 0);
Update: MAX Recursion option doesnt work with Inline table valued functions,it only works with multi table valued functions..
Demo:
alter function
dbo.getnum_test
(
@n int
)
returns table
as return
With cte as
(
select @n as n
union all
select @n+1
from cte
)
select * from cte
where n<1000
option (maxrecursion 0)
alter function dbo.itvftest
(
@n int
)
returns
@numbers table
(
n int
)
as
begin
With cte as
(
select @n as n
union all
select n+1
from cte
where cte.n<10000
)
Insert into @numbers
select * from cte
where n<1000
option (maxrecursion 0)
return
end
Upvotes: 3