如何以及在何处设置 MAXRECURSION 选项?
我有一个查询,它耗尽了默认的 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.
我发现我需要使用 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.
到目前为止,我已经尝试将它放在我定义 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)
相关文章