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