CODEWITHSUNDEEP
sql-serverrecursion
streamc
streamc

Reputation: 698

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I try to do SQL-1999 style recursion in SQL Server 

 CREATE TABLE addressparts(
    AddrPartId INTEGER NOT NULL,
    ParentID INTEGER NOT NULL,
    AddrPartTypeId VARCHAR(30),
    Name VARCHAR(30),
    constraint PK_OBJECTS primary key (AddrPartId))

insert into addressparts values (1, 0, 'country','Zimbabwe');
insert into addressparts values (2, 1, 'city','zi');
insert into addressparts values (3, 2, 'subcity','kalkli');
insert into addressparts values (4, 2, 'subcity','kalkli1');

I want select recursive query 

WITH 
 Rec (AddrPartId, ParentID, AddrPartTypeId, name)
 AS (
   SELECT AddrPartId, ParentID, AddrPartTypeId, name FROM addressparts
   UNION ALL
   SELECT Rec.AddrPartId, Rec.ParentID, rec.AddrPartTypeId, Rec.name
    FROM Rec
    inner join addressparts on Rec.AddrPartId = addressparts.ParentID
   )
 SELECT * FROM Rec 
 WHERE ParentID=0;

But I have error like in title

I want expected result as 

AddrPartId ParentID
1 0
2 1
3 2
4 2

by level

Upvotes: 0

Views: 10676

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

By default Recursive CTE will do 100 recursion.

Add option max recursion at the end of cte to increase the default recursion. But there are logical mistakes are there as well in your query. If am not wrong you are looking for something like this 

;WITH Rec (AddrPartId, ParentID, AddrPartTypeId, NAME)
     AS (SELECT AddrPartId,
                ParentID,
                AddrPartTypeId,
                NAME
         FROM   addressparts
         WHERE  ParentID = 0 -- add the parent id filter here 
         UNION ALL
         SELECT A.AddrPartId,  -- don't select from recursive cte 
                A.ParentID,
                A.AddrPartTypeId,
                A.NAME
         FROM   Rec
                INNER JOIN addressparts A
                        ON Rec.AddrPartId = A.ParentID)
SELECT *
FROM   Rec
OPTION (maxrecursion 0); -- here

Upvotes: 7

Related Questions