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:
Drop table if exists #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