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

Question

to setup the table and sample data:

USE [LDS]
GO
/****** Object:  Table [dbo].[Table_DownLineList]    Script Date: 11/14/2013 11:45:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_DownLineList](
      [Sr_No] [int] IDENTITY(1,1) NOT NULL,
      [Member_ID] [nvarchar](50) NULL,
      [Member_Name] [nvarchar](50) NULL,
      [Joining_Date] [nvarchar](50) NULL,
      [Upline_ID] [nvarchar](50) NULL,
      [Upline_Name] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [Placement_Leg] [nvarchar](50) NULL
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Table_DownLineList] ON

INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (1, N'LDS', N'LDS', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (2, N'LDS1', N'LDS', N'11-10-2013', N'LDS', N'LDS Raj', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (3, N'LDS2', N'LDS', N'11-10-2013', N'LDS', N'LDS Raj', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (4, N'SUNIL', N'SUNIL DUTT', N'11-10-2013', N'LDS1', N'LDS', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (5, N'SUNIL01', N'SUNIL DUTT', N'11-10-2013', N'SUNIL', N'SUNIL DUTT', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (6, N'SUNIL2', N'SUNIL DUTT', N'11-10-2013', N'SUNIL', N'SUNIL DUTT', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (7, N'RAJKISHOR', N'RAJKISHOR SHARMA', N'11-10-2013', N'SUNIL01', N'SUNIL DUTT', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (8, N'RAJKISHOR01', N'RAJKISHOR SHARMA', N'11-10-2013', N'RAJKISHOR', N'RAJKISHOR SHARMA', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (11, N'RAJA1', N'HARISH CHANDR', N'11-10-2013', N'RAJA', N'HARISH CHANDR', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (14, N'SURJEET1', N'SURJEET SINGH', N'11-10-2013', N'SURJEET', N'SURJEET SINGH', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (15, N'SURJEET2', N'SURJEET SINGH', N'11-10-2013', N'SURJEET', N'SURJEET SINGH', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (16, N'RAMASARE', N'RAMASARE', N'11-11-2013', N'SURJEET1', N'SURJEET SINGH', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (18, N'JITENDR', N'JITENDR KUMAR', N'11-11-2013', N'PRADEEP', N'PRADEEP KUMAR', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (19, N'JITENDR1', N'JITENDR KUMAR', N'11-11-2013', N'JITENDR', N'JITENDR KUMAR', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (21, N'RAJKUMARI', N'RAJKUMARI', N'11-11-2013', N'DHARAMVEER', N'DHARMVEER', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (23, N'RAMVEER', N'RAMVEER', N'11-11-2013', N'NEERAJA', N'NEERJA', N'DIBIYAPUR', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (26, N'SATEESH', N'SATISH KUMAR', N'11-11-2013', N'HARISWAROOP', N'HARISWAROOP', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (28, N'RAGHVENDR', N'RAGHVENDR SINGH', N'11-11-2013', N'GEETA', N'GEETA  DEVI', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (30, N'BALVEER', N'BALVEER SINGH', N'11-11-2013', N'RAJENDR', N'RAJENDR SINGH', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (31, N'LAYAK', N'LAYAK SINGH', N'11-11-2013', N'BALVEER', N'BALVEER SINGH', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (32, N'MULAYAM', N'MULAYAM SINGH', N'11-11-2013', N'LAYAK', N'LAYAK SINGH', N'ETAWAH', N'Left')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (33, N'RAJKUMAR', N'RAJKUMAR', N'11-11-2013', N'LAYAK', N'LAYAK SINGH', N'ETAWAH', N'Right')
INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (34, N'MOHAN', N'', N'11-11-2013', N'RAJKUMAR', N'RAJKUMAR', N'', N'Right')

and following my stored procedure

alter proc [dbo].[CountDownlineNode]
(
 @memberid nvarchar(50)
)
as
begin
;with cte as (
        select
              Member_Name,  Member_ID, Upline_ID, Placement_Leg,
                null lnode,
                null rnode
        from Table_DownLineList where Member_ID =@memberid     
        union all
        select
                t.Member_Name, t.Member_ID, t.Upline_ID, t.Placement_Leg,
                ISNULL(cte.lnode, CASE WHEN t.Placement_Leg = 'Left' THEN 1 ELSE 0 END) lnode,
                ISNULL(cte.rnode, CASE WHEN t.Placement_Leg  = 'Right' THEN 1 ELSE 0 END) rnode
        from Table_DownLineList t
        inner join cte
                on  cte.Member_ID=t.Upline_ID
)
select
        @memberid Member_ID,
        SUM(lnode) LeftNodes,
        SUM(rnode) RightNodes
        from cte   OPTION( MAXRECURSION 100)
end
GO

when we execute my stored procedure as:

exec CountDownlineNode 'LDS'

and I have found the following error:

Msg 530, Level 16, State 1, Procedure readDownlinedetails, Line 9
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

thanks for your suggestion but when we insert more data i have found the following error Msg 530, Level 16, State 1, Procedure readDownlinedetails, Line 9 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. after using OPTION( MAXRECURSION 100) at the end of CTE — user2342574, Nov 16 '13 at 08:48
possible duplicate of [The maximum recursion 100 has been exhausted before statement completion](http://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion) — Gerardo Lima, Apr 30 '15 at 15:36

score 1 · Answer 1 · answered Nov 15 '13 at 18:44

1

when I run your code above with no changes, I get:

Member_ID                                            LeftNodes  RightNodes
-------------------------------------------------- ----------- -----------
LDS                                                          6           1
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

to get more iterations in the CTE, change:

OPTION( MAXRECURSION 100)

to

OPTION( MAXRECURSION 0)

answered Nov 15 '13 at 18:44

KM.

101,727
34
178
212

when we use OPTION( MAXRECURSION 0) IN THE PLACE OF OPTION( MAXRECURSION 100) IT TAKE LONG TIME IN EXECUTION AND NOT RETURN ANY VALUE – user2342574 Nov 16 '13 at 11:37
when I run the code (even with `OPTION( MAXRECURSION 100)`) it completes quickly with the output I list in my answer. – KM. Nov 18 '13 at 16:14

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

1 Answers1

Linked