CTE查询引发递归用尽错误 [英] CTE Query throws Recursion Exhausted error

问题描述

以下CTE查询将引发语句终止。在语句完成之前，最大递归100已用尽。

The following CTE Query throws "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

WITH MyCTE
AS (
SELECT o.organizationid,
        organization AS organization 
FROM organization o
    INNER JOIN store s ON s.organizationid = o.organizationid
UNION ALL
SELECT store.storeid,
            CAST(storeNAme AS NVARCHAR(50)) AS storeNAme 
FROM store
    INNER JOIN MyCTE ON store.organizationid = MyCTE.organizationid)

    SELECT DISTINCT
            Organization
    FROM MyCTE

执行时

锚点查询：-
SELECT o.organizationid，
的组织AS的组织
来自组织机构o
内部联接商店s s.organizationid = o.organizationid
结果：-

Anchor query:- SELECT o.organizationid, organization AS organization FROM organization o INNER JOIN store s ON s.organizationid = o.organizationid Result:-

organizationid    |organization
--------------------------------
3                 | Org1

在合并全部后进行查询：-

query after union all:-

SELECT store.storeid，
CAST（storeNAme AS NVARCHAR（50））AS storeNAme
FROM store

SELECT store.storeid, CAST(storeNAme AS NVARCHAR(50)) AS storeNAme FROM store

结果：-

StoreId           |StoreName
--------------------------------
3                 | Warehouse1

我可以知道为什么吗？

推荐答案

在查询结束时指定maxrecursion选项：

Specify the maxrecursion option at the end of the query:

...
from MyCTE
option (maxrecursion 0)

您可以指定如何通常CTE可以在产生错误之前递归。 Maxrecursion 0允许无限递归。

That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.

这篇关于CTE查询引发递归用尽错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！