sql - TSQL:避免内联函数中的 MAXRECURSION 限制

Question

我有这个函数，给定初始日期和最终日期，给出该范围内相应的年/月:

CREATE FUNCTION [dbo].[fnYearMonth]
(
    @Initial Date,
    @Final Date 
)
RETURNS TABLE 
AS
RETURN
With dateRange(StatDate) as 
(
    select @Initial
    union all
    select dateadd(month, 1, StatDate)
    from dateRange 
    where dateadd(month, 1, StatDate) <= CAST(DATEADD(month,DATEDIFF(month,0,@Final)+1,0)-1 as Date)
)
select DATEPART(year, StatDate) AS MyYear, DATEPART(month, StatDate) AS MyMonth From dateRange where StatDate <= @Final

问题是 MAXRECURSION 的默认限制为 100 只能使可用的日期范围最大为 8 年零 4 个月。这还不够。

我尝试使用“OPTION (MAXRECURSION 2000);”在使用此函数的函数中，但这不起作用，因为我在WITH语句中调用了此函数。

我现在唯一的解决方案就是将这个内联函数变成多语句函数并使用“OPTION (MAXRECURSION 2000);”。但出于性能原因，我宁愿避免此选项。 ¿还有其他选择吗？

感谢您的帮助。

Answer 1

尝试在底部添加OPTION (MAXRECURSION 0)或您希望的递归限制，如下所示。

您还可以使用Calendar table以避免所有这些计算，从而提供您需要的输出..

我的数据库中填充了一个日历表，输出很容易计算，如下所示。.我建议使用一个表而不是重复计算

select distinct month,year from dbo.calendar
where dAte>=getdate()-200 and date<=getdate()

如果您想使用递归选项，请添加选项(递归)，如下所示

--这不适用于内联表值函数，请参见下面的演示 更改函数 [dbo].[fnYearMonth] ( @初始日期时间， @最终日期时间 ) 返回表 作为 返回 将日期范围设置为 ( 选择@Initial作为统计日期 联合所有 选择日期添加(月，1，StatDate) 从日期范围 其中 dateadd(月, 1, StatDate) <= CAST(DATEADD(月,DATEDIFF(月,0,@Final)+1,0)-1 作为日期时间) ) 选择 DATEPART(year, StatDate) AS MyYear, DATEPART(month, StatDate) AS MyMonth 从日期范围 其中 StatDate <= @Final 选项(最大递归0)；

更新:MAX Recursion 选项不适用于内联表值函数，它仅适用于多表值函数..

演示:

alter function 
dbo.getnum_test
(
@n int
)
returns table
as return
With cte as
(
select @n as n
union all
select @n+1
from cte
)

select * from cte
where  n<1000
option (maxrecursion 0)

alter function dbo.itvftest
(
@n int
)
returns
@numbers table
(
n int
)
as 
begin

With cte as
(
select @n as n
union all
select n+1
from cte
where cte.n<10000
)
Insert into @numbers
select * from cte
where  n<1000
option (maxrecursion 0)

return
end