CODEWITHSUNDEEP
sqlsql-serversplit
Bharath
Bharath

Reputation: 581

SPlit function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I have been using this below piece of code to split the input id like FUNCTION_split('1,2,3',','). Now the problem is it is not working for cases more than 100 in the list. Is there anyway to modify this piece of code to handle more than 100 id's?

CREATE FUNCTION [dbo].[FUNCTION_split]  
 (@list  NVARCHAR(MAX),  
  @delimiter NCHAR(1) = ',')   
    RETURNS TABLE   
AS  

RETURN  
    WITH cte_list([BeginChar], [EndChar]) AS (  
        SELECT [BeginChar] = CONVERT(BIGINT, 1), [EndChar] = CHARINDEX(@delimiter, @list + @delimiter)  
    UNION ALL  
        SELECT [BeginChar] = [EndChar] + 1, [EndChar] = CHARINDEX(@delimiter, @list + @delimiter, [EndChar] + 1)  
    FROM cte_list 
        WHERE  [EndChar] > 0  
    )  
    SELECT LTRIM(RTRIM(SUBSTRING(@list, [BeginChar],  
        CASE WHEN [EndChar] > 0 THEN [EndChar] - [BeginChar] ELSE 0 END))) AS [ParsedValue]  
    FROM cte_list 
        WHERE [EndChar] > 0 ;

Upvotes: 0

Views: 2700

Answers (1)

bummi
bummi

Reputation: 27385

You can use the option MAXRECURSION in your query

SELECT * FROM [dbo].[FUNCTION_split] ( @p1 , @p2 ) OPTION ( MAXRECURSION 30000 );

for unlimited recursion use Maxrecursion (0)

Query Hints

Upvotes: 1

Related Questions