CODEWITHSUNDEEP
sqlsql-servert-sqlinline-table-function
Ángel Javier Mena Espinosa
Ángel Javier Mena Espinosa

Reputation: 275

TSQL: avoid MAXRECURSION limit in a inline function

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

Answers (1)

TheGameiswar
TheGameiswar

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()

enter image description here

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

Related Questions