cte 上的 TSQL Maxrecursion [英] TSQL Maxrecursion on a cte

问题描述

我收到此错误:

最大递归100在语句前已用完完成

The maximum recursion 100 has been exhausted before statement completion

当我运行这个函数时:

    WITH allDays AS (

        SELECT @DateEarly AS date

        UNION ALL

        SELECT DATEADD(dd, 1, date) as date
        FROM allDays s  
        WHERE DATEADD(dd, 1, date) <= @DateLate


   )
    SELECT *
    from allDays 
    where dbo.isFestivo(date)>0

我尝试附加此选项:

OPTION (MAXRECURSION 200);

但我在选项"字样之前收到错误 156.你知道为什么吗?

but i get an error 156 before "option" word. Do you know why?

推荐答案

您可能将选项放在错误的位置.它需要在 where 之后

You're probably putting the option in the wrong place. It needs to be after the where

WITH allDays AS (
    SELECT @DateEarly AS date
    UNION ALL
    SELECT DATEADD(dd, 1, date) as date
    FROM allDays s  
    WHERE DATEADD(dd, 1, date) <= @DateLate
)
SELECT *
from allDays 
where dbo.isFestivo(date)>0
option (maxrecursion 200);

但是试试这个.会更快...

But try this instead. It'll be quicker...

select DATEADD(d, number, @dateearly) as [date]
from master..spt_values 
where type='p'
and number<=datediff(d,@dateearly,@datelate)
and dbo.isFestivo(date)>0

这篇关于cte 上的 TSQL Maxrecursion的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！