在 SQL 查询中显示语句完成错误之前，最大递归 100 已用尽 [英] The maximum recursion 100 has been exhausted before statement completion error showing in SQL Query

问题描述

SQL 查询中显示语句完成前最大递归 100 已用尽"错误

"The maximum recursion 100 has been exhausted before statement completion" error showing in SQL Query

WITH DepartmentCTE AS
(   SELECT  ID, 
        DepartmentName, 
        RootID, 
        RecursionLevel = 1, 
        ParentRoot = CAST('None' AS NVARCHAR(max)),
        LastParentCatID = RootID,
        DisplayOrder
FROM    Department
UNION ALL
SELECT  cte.ID, 
        cte.DepartmentName,
        cte.RootID,
        cte.RecursionLevel + 1,
        ParentRoot = CASE WHEN cte.RecursionLevel = 1 THEN '' ELSE cte.ParentRoot + '>' END + c.DepartmentName,
        LastParentCatID = c.RootID,
        cte.DisplayOrder
FROM    DepartmentCTE cte
        INNER JOIN Department c
            ON c.ID = cte.RootID

), MaxRecursion AS
(   SELECT  ID, 
        DepartmentName, 
        RootID, 
        ParentRoot, 
        RowNum = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RecursionLevel DESC),
        DisplayOrder
FROM    DepartmentCTE
)
SELECT  ID, DepartmentName, RootID, ParentRoot
FROM    MaxRecursion 
WHERE   RowNum = 1;

推荐答案

您可以使用 MAXRECURSION 选项提示限制递归级别的数量，如下所示:OPTION (MAXRECURSION 0); 其中值(0 到 32767 之间)指定递归的级别数，0 表示无限.

You can limit the number of recursion levels using the MAXRECURSION option hint like this: OPTION (MAXRECURSION 0); where the value (between 0 and 32767) specifies the number of levels of recursion, 0 meaning infinite.

来自 CTE 的文档:

错误的递归 CTE 可能会导致无限循环.为了例如，如果递归成员查询定义返回相同的父列和子列的值，无限循环是创建.为了防止无限循环，您可以限制数量通过使用允许特定语句的递归级别MAXRECURSION 提示和 OPTION 中 0 到 32,767 之间的值INSERT、UPDATE、DELETE 或 SELECT 语句的子句.这让您控制语句的执行，直到您解析代码创建循环的问题.服务器范围的默认值为 100.指定 0 时，不应用任何限制.只有一个 MAXRECURSION 值可以指定每个语句.有关更多信息，请参阅查询提示(Transact-SQL).

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).

查询提示文档指出:

最大递归数

指定此查询允许的最大递归数.Number 是一个介于 0 和 32767 之间的非负整数.当 0 是指定，没有限制.如果未指定此选项，则服务器的默认限制为 100.

Specifies the maximum number of recursions allowed for this query. Number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

当查询执行期间达到 MAXRECURSION 限制的指定或默认数量时，查询结束并出现错误返回.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

因为这个错误，语句的所有效果都回滚了.如果语句是 SELECT 语句，部分结果或没有结果可能会返回.返回的任何部分结果可能不包括超出指定最大递归的递归级别上的所有行水平.

Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

要使用该语句，请使用递归 CTE 在查询中的 FROM 子句之后附加 OPTION 子句.

To use the statement you append the OPTION clause after the FROM clause in the query using the recursive CTE.

如果查询进入无限循环，指定 0 可能会导致不好的东西.

Specifying 0 might lead to bad stuff if the query goes into an infinite loop though.

这篇关于在 SQL 查询中显示语句完成错误之前，最大递归 100 已用尽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！