title: The statement terminated. The maximum recursion 100 has been exhausted before statement completion
body: <p>to setup the table and sample data:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>and following my stored procedure</p>
  
  <pre><code>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
  </code></pre>
  
  <p>when we execute my stored procedure as:</p>
  
  <pre><code>exec CountDownlineNode 'LDS'
  </code></pre>
  
  <p>and I have found the following error:</p>
  
  <blockquote>
    <p>Msg 530, Level 16, State 1, Procedure readDownlinedetails, Line 9<br>
    The statement terminated. The maximum recursion 100 has been exhausted
    before statement completion.</p>
  </blockquote>