sql-server - 如何为表值函数内的 CTE 设置 maxrecursion 选项

Question

我在声明 TVF 内的 CTE 的 maxrecursion 选项时遇到问题。

这是 CTE(一个简单的日历):

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)

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

上面的 TVF 在没有 maxrecursion 选项的情况下运行正常但该选项存在语法错误。解决办法是什么？

Answer 1

来自this MSDN forums thread我了解到

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

以及进一步

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.

因此，在您的示例中，您在调用函数时必须指定OPTION:

 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
 )

(稍后)

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

请注意，您无法通过使用第二个 TVF 来解决此问题，该 TVF 仅执行上述行 - 如果您尝试，您会得到相同的错误。 “[the] OPTION 子句只能在语句级别使用”，这是最终的(目前)。