how to use recursive cte in table valued function that return more than 100 records

Question

0.00/5 (No votes)

See more:

XML

hi all,


i am trying to create a table valued  <u>SPLIT </u>function that uses CTE's recursive functionality to get records from the user parameters. the function is returning the values for parameter as expected if the parameter value (accounts) count is less than 100 and if the number of user parameter (accounts) are more than 100 then the function is failing and returning an error message: <b>maximum recursion 100 reached.</b>

If anyone can give a solution or a better method to implement the same logic is appreciated.

Posted 27-Apr-15 23:45pm

Member 11052008

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-04-28T00:18:00

Solution 1

Use:

SQL

;WITH CTE AS
(
    --select
)
SELECT 
FROM CTE
OPTION (MAXRECURSION 0)  --here!

See: WITH common_table_expression (Transact-SQL)[^]

Posted 28-Apr-15 0:18am

Maciej Los

Comments

Member 11052008 28-Apr-15 6:50am

Thanks for the reply.

my query follows like below:

;with CTE_Account As (

select left(@temp_Accounts,charindex('.',@temp_Accounts)-1) as Account_Id_Level,

stuff(@temp_Accounts,1,charindex('.',@temp_Accounts),'') as rest_str,

substring(@temp_Accounts,charindex(',',@temp_Accounts)+1,7000) as rest_Accounts

Union All

select left(rest_Accounts,charindex('.',rest_Accounts)-1) as Account_Id_Level,

stuff(rest_Accounts,1,charindex('.',rest_Accounts),'') as rest_str,

case when charindex(',',rest_Accounts) <> 0 then substring(rest_Accounts,charindex(',',rest_Accounts)+1,7000)

else '' END as rest_Accounts

from CTE_Account where rest_Accounts <> ''

),

Cte_Source_Account_Codes
as
(

Select distinct Convert(int,Account_ID_LEVEL) AS Source_Account_id,Ods_Instance_ID as Source_Database_id, Level as Account_Level_Number
from (

select *,cast(substring(rest_str,1,charindex('.',rest_str)-1) as int) as Ods_Instance_Id,

cast(substring(rest_str,charindex('.',rest_str)+1,1) as int) as level

from (select Account_Id_Level,rest_str,rest_Accounts from CTE_Account) b
) a
)

If you have any idea where to give the hint: option (maxrecursion 1000). While browsing on this issue,i got to know that no table hint is allowed in recursive cte in UDF.

Please let me know if you have any idea on this or some other solution related to this issue.