How to set the maxrecursion option for a CTE inside a Table-Valued-Function

Question

I'm facing a problem declaring the maxrecursion option for a CTE inside a TVF.

Here is the CTE (a simple calendar):

DECLARE @DEBUT DATE = '1/1/11',   @FIN DATE = '1/10/11';

WITH CTE as(       
SELECT @debut as jour       
UNION ALL       
SELECT DATEADD(day, 1, jour)       
FROM   CTE      
WHERE  DATEADD(day, 1, jour) <= @fin)
SELECT jour FROM CTE option (maxrecursion 365)

and the TVF:

 CREATE FUNCTION [liste_jour]  
 (@debut date,@fin date)
 RETURNS TABLE
 AS     
 RETURN      
 (  
  WITH CTE as(       
  SELECT @debut as jour       
  UNION  ALL       
  SELECT DATEADD(day, 1, jour)       
  FROM   CTE      
  WHERE  DATEADD(day, 1, jour) <= @fin)
  SELECT jour FROM CTE
  --option (maxrecursion 365)
 )

The above TVF is running OK without the maxrecursion option but there is a syntax error with the option. What is the solution?

score 49 · Accepted Answer · answered Sep 15 '11 at 09:45

From this MSDN forums thread I learn that

[the] OPTION clause can be used only at the statement level

So you cannot use it within a query expression inside view definitions or inline TVFs etc. The only way to use it in your case is to create the TVF without the OPTION clause and specify it in the query that uses the TVF. We have a bug that tracks request for allowing use of OPTION clause inside any query expression (for example, if exists() or CTE or view).

and further

You can not change the default value of that option inside a udf. You will have to do it in the statement referencing the udf.

So in your example, you must specify the OPTION when you call your function:

 CREATE FUNCTION [liste_jour]  
 (@debut date,@fin date)
 RETURNS TABLE
 AS     
 RETURN      
 (  
  WITH CTE as(       
  SELECT @debut as jour       
  UNION  ALL       
  SELECT DATEADD(day, 1, jour)       
  FROM   CTE      
  WHERE  DATEADD(day, 1, jour) <= @fin)
  SELECT jour FROM CTE -- no OPTION here
 )

(later)

SELECT * FROM [liste_jour] ( @from , @to ) OPTION ( MAXRECURSION 365 )

Note that you can't work round this by having a second TVF that just does the above line - you get the same error, if you try. "[the] OPTION clause can be used only at the statement level", and that's final (for now).

score 25 · Answer 2 · answered Jun 07 '12 at 19:10

25

Old thread, I know, but I needed the same thing and just dealt with it by using a multi-statement UDF:

CREATE FUNCTION DatesInRange
(
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS 
@ReturnVal TABLE 
(
    date datetime
)
AS
BEGIN

    with DateTable as (
        select dateFrom = @DateFrom

        union all

        select DateAdd(day, 1, df.dateFrom)
        from DateTable df
        where df.dateFrom < @DateTo
    )
    insert into @ReturnVal(date)

    select dateFrom

    from DateTable option (maxrecursion 32767)

    RETURN 
END
GO

There are probably efficiency issues with this, but I can afford it in my case.

answered Jun 07 '12 at 19:10

Chris Pfohl

18,220
9
68
111

3

This answer may be a little bit better, because there is no need to remember about CTE (without recursion limit) in a function. – Michał Powaga Mar 15 '13 at 09:07
1

Of course it solves problem, but it has "possibly a drawback", because this table-valued function is `non-inlined`. It may be crusial in terms of performance. – pkuderov Jan 30 '18 at 15:24
1

As I said, "There are probably efficiency issues with this, but I can afford it in my case." – Chris Pfohl Jan 30 '18 at 16:06

mattmc3 · Answer 3 · 2019-10-05T01:48:49.863

A little creative use of CTEs and cartesian products (cross joins) will get you around the MAXRECURSION limit of 100. 3 CTEs with a limit of 4 records on the last one nets you 40,000 records, which will be good for more than 100 years worth of data. If you expect more difference between @debut and @fin, you can adjust cte3.

-- please don't SHOUTCASE your SQL anymore... this ain't COBOL
alter function liste_jour(@debut date, @fin date) returns table as
return (  
    with cte as (
        select 0 as seq1
        union all
        select seq1 + 1
        from cte
        where seq1 + 1 < 100
    ),
    cte2 as (
        select 0 as seq2
        union all
        select seq2 + 1
        from cte2
        where seq2 + 1 < 100
    ),
    cte3 as (
        select 0 as seq3
        union all
        select seq3 + 1
        from cte3
        where seq3 + 1 <= 3 -- increase if 100 years isn't good enough
    )
    select
        dateadd(day, (seq1 + (100 * seq2) + (10000 * seq3)), @debut) as jour
    from cte, cte2, cte3
    where (seq1 + (100 * seq2) + (10000 * seq3)) <= datediff(day, @debut, @fin)
)
go
-- test it!
select * from liste_jour('1/1/2000', '2/1/2000')

I SO agree with the `please don't SHOUTCASE your SQL anymore... this ain't COBOL` comment — mono blaine, Jan 23 '20 at 13:21

score 3 · Answer 4 · edited Apr 28 '20 at 14:28

Old issue but... I just wanted to clarify why OPTION(MAXRECURSION x) is not allowed in an in-line table-valued function. This is because iTVF's get inlined when you use them in a query. And, as we all know, you cannot put this option anywhere else save at the very end of the query. This is THE reason it will never be possible to put it inside an iTVF (unless the parser and/or algebrizer does some magic behind the scenes, which I don't think it will any time soon). mTVF's (multi-statement table-valued functions) is a different story because they don't get inlined (and are so slow that they should never be used in queries; it's OK to use them in an assignment to a variable, though, but then again---beware of loops!).

score 2 · Answer 5 · answered Aug 25 '15 at 23:47

Another way to handle this is to break up the problem into a pair of CTEs, neither of which hits the recursion limit of 100. The first CTE creates a list with the begin date for each month in the range. The second CTE then fills in all the days of each month. As long as the input range is fewer than 100 months, it should work fine. If an input range of greater than 100 months is required, the same idea could be expanded with a third CTE for years added ahead of the months CTE.

CREATE FUNCTION [liste_jour]    
(@debut datetime, @fin datetime)    
RETURNS TABLE   
AS      
RETURN          
(   
    WITH CTE_MOIS AS
    (           
        SELECT JOUR_DEBUT = @debut
        UNION ALL
        SELECT DATEADD(MONTH, 1, CTE_MOIS.JOUR_DEBUT)
          FROM CTE_MOIS         
         WHERE DATEADD(MONTH, 1, CTE_MOIS.JOUR_DEBUT) <= @fin
    ),

    CTE_JOUR AS
    (           
        SELECT JOUR = CTE_MOIS.JOUR_DEBUT
          FROM CTE_MOIS
        UNION ALL           
        SELECT DATEADD(DAY, 1, CTE_JOUR.JOUR)
          FROM CTE_JOUR
         WHERE MONTH(CTE_JOUR.JOUR) = MONTH(DATEADD(DAY, 1, CTE_JOUR.JOUR)) AND
            DATEADD(DAY, 1, CTE_JOUR.JOUR) <= @FIN
    )

    SELECT JOUR
      FROM CTE_JOUR
)

score 0 · Answer 6 · edited Apr 28 '20 at 14:26

create simple sample for you :)

/* block create function for test in sql*/
/*FUNCTION [fn_CTE_withLevel] (@max_level int)
RETURNS TABLE
  AS
 RETURN  
       ( */


 /*******************   declare table just replace real table   *****/
declare @tbl table(pid varchar(15),id varchar(15))

/* use function argument */
declare @max_level int = 3

Insert Into @tbl(pid , id)
   values 

     /*lev1*/   ('0','1') ,
         /*lev2*/   ('1','101') ,
         /*lev2*/   ('1','102') ,
     /*lev1*/   ('0','2') ,
         /*lev2*/   ('2','201') ,
                 /*lev3*/   ('201','20101') ,
                 /*lev3*/   ('201','20102') ,
         /*lev2*/   ('2','202') ,
     /*lev1*/   ('0','3') ,
         /*lev2*/   ('3','301') ,
         /*lev2*/   ('3','302') ,
     /*lev1*/   ('0','4') ,
        /*lev2*/    ('4','401'),
        /*lev2*/    ('4','402');

 /*******************   declare table just replace real table   *****/

  With cte_result(pid , id , lev)
        As(
            Select pid , id , 1 as lev From @tbl t
              Where pid = '0'  /* change to another values from list to test sub items */

              Union All

            Select t.pid , t.id , cte.lev + 1 as lev
                 From  cte_result cte
                        inner Join  @tbl t
                  On  cte.id = t.pid 
                   Where cte.lev < @max_level  -- :) this is my idea
          )

         Select * From cte_result 
             --OPTION (MAXRECURSION 100)

  -- uncomment for create function
 /*)*/

Please correct formatting of the code and explain a bit what it does... — aschipfl, Jul 02 '18 at 16:24

How to set the maxrecursion option for a CTE inside a Table-Valued-Function

6 Answers6

Linked