問題描述

我有一個查詢，它耗盡了默認的 MAXRECURSION 限制 100.給我以下錯誤消息:

I have a query that exhausts the default MAXRECURSION limit of 100. Giving me the following error message:

語句終止.最大遞歸 100 在語句完成前已用完.

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

我發(fā)現(xiàn)我需要使用 OPTION (MAXRECURSION xxx) 來提高這個 CTE 的限制，但我不知道把它放在哪里.

I have found out that I need to raise the limit for this CTE using OPTION (MAXRECURSION xxx) but I don't know where to put this.

到目前為止，我已經(jīng)嘗試將它放在我定義 CTE 的位置旁邊，但它不起作用.我也試過幾個不同的地方，它也不起作用.我每次得到的錯誤是:

So far I've tried to put it next to where I define the CTE but it isn't working. I've also tried a few different places and it's not working either. The error I get each time is:

關鍵字OPTION"附近的語法不正確.

Incorrect syntax near the keyword 'OPTION'.

那么我應該將 OPTION (MAXRECURSION XXX) 命令放在我的 SQL 中的什么位置?

So where should I put the OPTION (MAXRECURSION XXX) command in my SQL?

with
    tab (id,start,en) AS (
                  SELECT 1, 100, 200
        UNION ALL SELECT 2, 200, 500
    ),
    cte (id,start,en) AS (
        SELECT id, start, en FROM tab
        UNION ALL
        SELECT id, start+1, en FROM cte WHERE start+1 <= en
    )
SELECT id, start
FROM cte
ORDER BY id

推薦答案

with tab AS 
(
    select 1 as id, 100 as start, 200 as en
    union all
    select 2, 200, 500),
    cte AS
    (
        select id,start,en from tab
        union all
        select id,start+1 , en from cte where start+1<=en
    )

SELECT id,start from cte
order by id
OPTION (MAXRECURSION 1000)

這篇關于如何以及在何處設置 MAXRECURSION 選項?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！