TSQL: avoid MAXRECURSION limit in a inline function

Question

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.

You must use OPTION (MAXRECURSION ...) on the very last query. For example I got function with recursive cte, I run this function in another cte and then select from it. I will use OPTION (MAXRECURSION ...) on the last SELECT. — gofr1
– gofr1, Commented Jun 15, 2016 at 9:20

TheGameiswar · Accepted Answer · 2016-06-15 10:50:02Z

3

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

edited Jun 15, 2016 at 10:50

answered Jun 15, 2016 at 9:27

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ángel Javier Mena Espinosa Over a year ago

Is a good idea to create a table of dates an insert 100 (years) * 365(days) = 36500 rows? I think that those are too many rows. And the second option is not possible. In inline functions the OPTION statement is not available. Sql management studio gives a syntax error.

TheGameiswar Over a year ago

I think creating table is a good idea,i have tried with recursive cte,let me try with inline functions and update the question

gotqn Over a year ago

@ajmena 40k rows of int values is nothing. And reading the data from the table will be faster than executing the function constantly.

TheGameiswar Over a year ago

It seems only multi table valued functions are supported,I would go with a table option only because it can help you in lot of instances other than this

Liesel Over a year ago

@ajmena A date table (and a time table) are def the way to go. brentozar.com/archive/2014/12/simply-must-date-table-video

|

Collectives™ on Stack Overflow

TSQL: avoid MAXRECURSION limit in a inline function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related