MAXRECURSION 0 Option

  • 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

  • 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

  • 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

  • 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

  • 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?

  • 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?

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

  • 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

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

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