Increase maximum recursion depth when querying table-valued function

Question

I am calling a recursive table valued function like this:

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

I receive the following error:

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

It turns out the data is such that it needs to go deeper than 32. Is it possible to change the depth for this single call?

Edit: BTW, this does not work:

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

Even when I specify the MAXRECURSION hint I still get the same error message (with limit 32).

Answer 1

You have hit the limit to "nesting level" in SQL Server. This is a limit on how deep the call tree of subprograms (user defined functions, stored procedures etc.) can be. For SQL Server 2014 this limit is still 32. "When the maximum of 32 [nesting levels] is exceeded, the transaction is terminated" This limit applies, even when there is no recursion. (Example to follow.)

MAXRECURSION only applies to recursive common table expressions (CTE). Here the recursion is within a select statement and is not invoking a call to another subprogram. Note, there appears to be some weirdness when the recursive CTE is inside a function. See How to setup the maxrecursion option for a CTE inside a Table-Valued-Function

Recursive CTE, using 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)

Exceeding Nesting Levels without recursion

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).