使用OPTION(MAXRECURSION 0)仍触发递归耗尽错误的SQL函数问题

函数内部的递归CTE dates0 用于生成一年的日期（365天），但SQL Server中用户定义函数内的递归CTE默认递归上限是100，且外部查询的 OPTION(MAXRECURSION 0) 无法作用于函数内部的递归逻辑。当递归次数超过100时，就会触发报错。

替换递归的日期生成逻辑为非递归方式，避免触发递归限制。推荐使用系统视图 master..spt_values 或自定义数字表来生成日期范围。

修改后的完整函数代码 #

ALTER FUNCTION [dbo].[DataFimPrevisto] (@tempoPrevisto real, @DataIni datetime)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @DataFim datetime;
    DECLARE @calculo TABLE( xend datetime, [minutes] int);
    WITH 
    drange (date_start, date_end) AS 
    (      
        SELECT 
        CAST(@DataIni AS DATE) AS date_start, 
        CAST(DATEADD( YEAR, 1, @DataIni) AS DATE) AS date_end
    ), 
    -- 替换递归CTE为非递归日期生成
    dates0 (adate) AS 
    (                     
        SELECT DATEADD(day, n.number, drange.date_start) AS adate
        FROM drange
        JOIN master..spt_values n ON n.type = 'P' 
        WHERE n.number <= DATEDIFF(day, drange.date_start, drange.date_end)
    ), 
    dates (adate) AS 
    (                                
        SELECT adate FROM dates0 
        WHERE DATEPART(dw , adate) NOT IN ('1', '7') AND NOT EXISTS( SELECT 1 FROM BAS_PeriodosExcecoes B WHERE B.Trabalhavel = 0 AND B.DataInicio = adate)
    ), 
    hours (hour_start, hour_end) AS 
    (                  
        SELECT  8.5*60, 12.5*60 
        UNION
        SELECT 13.5*60, 18*60
    ), 
    hours_friday (hour_start, hour_end) AS 
    (                  
        SELECT  8*60, 14*60
    ), 
    datehours (xstart, xend) AS 
    (   
        SELECT * 
        FROM
        (
            SELECT 
            DATEADD(minute, hour_start, CAST(adate AS datetime)) xstart, 
            DATEADD(minute, hour_end  , CAST(adate AS datetime)) xend
            FROM dates AS d, hours AS h
            WHERE DATEPART(dw , adate) <> '6'

            UNION

            SELECT T2.xstart, T2.xend
            FROM
            (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY T.xstart ORDER BY T.xend ASC) AS rank 
                FROM
                (
                    SELECT 
                    @DataIni xstart, 
                    DATEADD(minute, hour_end, CAST(adate AS datetime)) xend
                    FROM dates AS d, hours AS h
                    WHERE adate = CAST( @DataIni AS DATE) AND DATEADD(minute, hour_end, CAST(adate AS datetime)) > @DataIni AND DATEPART(dw , adate) <> '6'
                ) T 
            ) T2 
            WHERE T2.rank = 1

            UNION

            SELECT 
            DATEADD(minute, hour_start, CAST(adate AS datetime)) xstart, 
            DATEADD(minute, hour_end  , CAST(adate AS datetime)) xend
            FROM dates AS d, hours_friday AS h
            WHERE DATEPART(dw , adate) = '6'

            UNION

            SELECT T2.xstart, T2.xend
            FROM
            (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY T.xstart ORDER BY T.xend ASC) AS rank 
                FROM
                (
                    SELECT 
                    @DataIni xstart, 
                    DATEADD(minute, hour_end, CAST(adate AS datetime)) xend
                    FROM dates AS d, hours_friday AS h
                    WHERE adate = CAST( @DataIni AS DATE) AND DATEADD(minute, hour_end, CAST(adate AS datetime)) > @DataIni AND DATEPART(dw , adate) = '6'
                ) T 
            ) T2 
            WHERE T2.rank = 1
        ) T3 WHERE T3.xstart >= @DataIni
    ),
    cumulative (xend, [minutes]) AS
    (
        SELECT t.xend, SUM(DATEDIFF(MINUTE, xstart, xend)) OVER (ORDER BY xstart) AS [minutes]
        FROM datehours AS t
    )
    INSERT INTO @calculo
    SELECT TOP 1 xend, [minutes]
    FROM cumulative
    WHERE [minutes] >= @tempoPrevisto
    ORDER BY cumulative.xend ASC;

    SET @DataFim = (SELECT DATEADD( MINUTE, @tempoPrevisto - MAX([minutes]), MAX( [xend])) FROM @calculo);
    RETURN(@DataFim);
END;

关键修改说明 #

• 将递归生成日期的 dates0 CTE 替换为基于 master..spt_values 的非递归逻辑，该视图提供了0到2047的连续数字，足以覆盖一年的日期范围。
• 若需要生成超过2047天的日期，可以自定义数字表扩展范围，例如：

  numbers(n) AS (
      SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ),
  numbers_range(n) AS (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 
      FROM numbers a, numbers b, numbers c -- 生成0-999的数字，可继续交叉连接扩展
  ),
  dates0 (adate) AS (
      SELECT DATEADD(day, nr.n, drange.date_start) AS adate
      FROM drange
      JOIN numbers_range nr ON nr.n <= DATEDIFF(day, drange.date_start, drange.date_end)
  )
  

测试验证 #

执行原测试语句即可正常运行，无需额外设置递归选项：

SELECT dbo.DataFimPrevisto( 21240, DATETIMEFROMPARTS( 2023, 1, 25, 6, 0, 0, 0));

内容的提问来源于stack exchange，提问作者GoncaloCC