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?

Answer 1

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

Answer 2

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.