MAXRECURSION 0 Option

Question

MAXRECURSION 0 Option

Scott745618

SSC Enthusiast

Points: 197
More actions
June 9, 2021 at 11:50 pm

#3894148
Hi Everyone,
I am trying to apply a Multi table function that uses a recursive CTE. When I apply it I get the error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I've looked online about this error and apparently I need to add: Option ( MAXRECURSION 0 )
Apparently I cannot add it to my UDF I need to add it to the statement that calls the UDF. Unfortunately it does not allow me to add this option anywhere in my statement in SQL server management studio. My code is below:
```
Use sand
Drop table if exists #testfunction;
Select b.*
into #testfunction
From ( select distinct H_account from sand.HL_test_function ) a
Cross apply [corp\sgraham].udf_HL_term_clean(a.H_account) b
Order by b.H_account, b.sequence
```
Where can I add the Option ( MAXRECURSION 0 ) to resolve the error I am getting?
Thanks for any help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

You can use the option (maxrecursion 0) at the SQL statement that uses your table valued function. Here is an example:

CREATE or alter FUNCTION  Demo
(
    @FirstNum int,
    @LastNum int
)
RETURNS TABLE
AS
return 

    with MyCTE as (
        select @FirstNum as num
        union all
select num + 1 from MyCTE
where num < @LastNum
    )
    select num
    from MyCTE
go

select * 
from dbo.Demo(1,150) As FuncDemo  inner join sys.objects so on FuncDemo.num = so.object_id
option (maxrecursion 0) --The end of the sql statement that is using the function
go

drop function dbo.Demo

Adi

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 2

Can you show me on my code? When I try to add the maxrecursion it does not work

Use sand
Drop table if exists #testfunction;
Select b.*
into #testfunction
From ( select distinct H_account from sand.HL_test_function ) a
Cross apply [corp\sgraham].udf_HL_term_clean(a.H_account) b Option (Maxrecursion 0)
Order by b.H_account, b.sequence

Thanks

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 3

You tried to add it in the middle of your SQL Statement. Do it after the order by clause

Use sand
Drop table if exists #testfunction;
Select b.*
into #testfunction
From ( select distinct H_account from sand.HL_test_function ) a
Cross apply [corp\sgraham].udf_HL_term_clean(a.H_account) b 
Order by b.H_account, b.sequence
Option (Maxrecursion 0)

Adi

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 4

I tried that and it ran (unlike when I put the option in other parts of the code) but I still got the same error. Any ideas as to why? and how I can solve this?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Scott745618 wrote:

I tried that and it ran (unlike when I put the option in other parts of the code) but I still got the same error. Any ideas as to why? and how I can solve this?

Exactly what is the error you are getting when you put Option (Maxrecursion 0) at the end of the statement?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Scott745618 wrote:

Hi Everyone,
I am trying to apply a Multi table function that uses a recursive CTE. When I apply it I get the error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I've looked online about this error and apparently I need to add: Option ( MAXRECURSION 0 )
Apparently I cannot add it to my UDF I need to add it to the statement that calls the UDF. Unfortunately it does not allow me to add this option anywhere in my statement in SQL server management studio. My code is below:
Use sand
Drop table if exists #testfunction;
Select b.*
into #testfunction
From ( select distinct H_account from sand.HL_test_function ) a
Cross apply [corp\sgraham].udf_HL_term_clean(a.H_account) b
Order by b.H_account, b.sequence
Where can I add the Option ( MAXRECURSION 0 ) to resolve the error I am getting?
Thanks for any help

It might be helpful if you post the code for the function. There may be a way to do what you want without having to resort the the RBAR of a recursive CTE (rCTE).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 7

I’ve added the option within the function itself and it’s working now. Everything I read online said I could not do that ??

sometimes You just have to try things even if it says you can’t.

unfortunately the function is not working properly so back to the drawing board. I might post in this forum if I cannot resolve. I’m pulling my hair out.

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 8

Scott745618 wrote:

I’ve added the option within the function itself and it’s working now. Everything I read online said I could not do that ??
sometimes You just have to try things even if it says you can’t.
unfortunately the function is not working properly so back to the drawing board. I might post in this forum if I cannot resolve. I’m pulling my hair out.

It depends on the type of table valued function that you are using. If you are using multi line table valued function, then you can use the maxrecursion option in the function, but on inline table valued function, you can't use the maxrecursion option.

Adi