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

common-table-expression, sql-server

Solution

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

Problem

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?

Original source