sql server - 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?

question from:https://stackoverflow.com/questions/7428669/how-to-set-the-maxrecursion-option-for-a-cte-inside-a-table-valued-function

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