SQL Server递归查询超出最大递归100限制报错求助

我编写的以下查询在日期范围少于100天时可正常运行：

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @cols AS NVARCHAR(max)
DECLARE @query AS NVARCHAR(max)
DECLARE @startDate1 NVARCHAR(max)
DECLARE @endDate1 NVARCHAR(max)

SET @startDate = '2023/04/01'
SET @endDate = '2023/12/31'
SET @startDate1 = '2023/04/01'
SET @endDate1 = '2023/12/31';

WITH dates(date) AS 
(
    SELECT @startdate AS Date
    UNION ALL
    SELECT DATEADD(d, 1, [date])
    FROM dates 
    WHERE date < @enddate 
)
SELECT 
    @cols = STUFF((SELECT ',' + Quotename((CONVERT(VARCHAR(100), [date], 103))) AS [Date]
                   FROM dates 
                   FOR XML PATH(''), TYPE).Value('.', 'NVARCHAR(MAX)'), 1, 1, '')

但当日期范围超过100天时，出现如下错误：

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

我尝试使用option (maxrecursion 0)来解决，但在SQL Server中提示语法错误，请问该如何解决这个问题？

SQL Server的递归CTE默认最大递归次数是100，日期范围超过100天就会触发这个限制。之前加option (maxrecursion 0)报错是因为放置位置错误——这个查询提示必须放在整个查询语句的最后，不能嵌入CTE定义内部。

修改后的可用代码如下：

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @cols AS NVARCHAR(max)
DECLARE @query AS NVARCHAR(max)
DECLARE @startDate1 NVARCHAR(max)
DECLARE @endDate1 NVARCHAR(max)

SET @startDate = '2023/04/01'
SET @endDate = '2023/12/31'
SET @startDate1 = '2023/04/01'
SET @endDate1 = '2023/12/31';

WITH dates(date) AS 
(
    SELECT @startdate AS Date
    UNION ALL
    SELECT DATEADD(d, 1, [date])
    FROM dates 
    WHERE date < @enddate 
)
SELECT 
    @cols = STUFF((SELECT ',' + Quotename((CONVERT(VARCHAR(100), [date], 103))) AS [Date]
                   FROM dates 
                   FOR XML PATH(''), TYPE).Value('.', 'NVARCHAR(MAX)'), 1, 1, '')
OPTION (MAXRECURSION 0); -- 必须放在整个SELECT语句的末尾

补充说明：

• OPTION (MAXRECURSION 0)表示取消递归次数限制，允许CTE递归任意次数（只要不超出SQL Server的其他资源限制）
• 若不想完全取消限制，也可以设置一个足够覆盖需求的数值（比如一年365天），写成OPTION (MAXRECURSION 365)，既能满足日期范围需求，又能避免无限制递归的风险

内容的提问来源于stack exchange，提问作者Anup