sql-server - 查询表值函数时增加最大递归深度

Question

我正在调用这样的递归表值函数:

SELECT * FROM dbo.Some_TVF(@param1, @param2)

我收到以下错误:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

事实证明，数据需要比 32 更深。是否可以更改此单个调用的深度？

编辑:顺便说一句，这不起作用:

SELECT * FROM dbo.Some_TVF(@param1, @param2) OPTION (MAXRECURSION 100)

即使我指定了 MAXRECURSION提示我仍然收到相同的错误消息(限制为 32)。

Answer 1

您已达到 SQL Server 中“嵌套级别”的限制。这是对子程序(用户定义的函数、存储过程等)的调用树的深度的限制。对于 SQL Server 2014，此限制仍为 32。"When the maximum of 32 [nesting levels] is exceeded, the transaction is terminated"即使没有递归，此限制也适用。 (以下示例。)

MAXRECURSION 仅适用于递归公用表表达式 (CTE)。这里的递归是在一个 select 语句中，而不是调用另一个子程序。请注意，当递归 CTE 位于函数内部时，似乎有些奇怪。见 How to setup the maxrecursion option for a CTE inside a Table-Valued-Function

递归 CTE，使用 MAXRECURSION

WITH alfa AS (SELECT 1 AS Val)
, bravo AS (SELECT val, 0 AS level 
    FROM alfa
    UNION ALL
    SELECT val, level + 1 
    FROM bravo
    WHERE level <= 100)
SELECT MAX(level)
FROM bravo
OPTION (MAXRECURSION 101)

超过嵌套级别而不递归

create procedure p33 as 
    select 'Hello World'
go

create procedure p32 as exec p33
go

create procedure p31 as exec p32
go

create procedure p30 as exec p31
go

create procedure p29 as exec p30
go

create procedure p28 as exec p29
go

create procedure p27 as exec p28
go

create procedure p26 as exec p27
go

create procedure p25 as exec p26
go

create procedure p24 as exec p25
go

create procedure p23 as exec p24
go

create procedure p22 as exec p23
go

create procedure p21 as exec p22
go

create procedure p20 as exec p21
go

create procedure p19 as exec p20
go

create procedure p18 as exec p19
go

create procedure p17 as exec p18
go

create procedure p16 as exec p17
go

create procedure p15 as exec p16
go

create procedure p14 as exec p15
go

create procedure p13 as exec p14
go

create procedure p12 as exec p13
go

create procedure p11 as exec p12
go

create procedure p10 as exec p11
go

create procedure p9 as exec p10
go

create procedure p8 as exec p9
go

create procedure p7 as exec p8
go

create procedure p6 as exec p7
go

create procedure p5 as exec p6
go

create procedure p4 as exec p5
go

create procedure p3 as exec p4
go

create procedure p2 as exec p3
go

create procedure p1 as exec p2
go

exec p1

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).