none
The maximum recursion 100 has been exhausted before statement completion

    Question

  • hi  following is the my table , data and stored procedure but when we execute my stored procedure as follows

    exec CountDownlineNode 'LDS'

    it does not show any record and it take infinite time in execution but when we use  OPTION( MAXRECURSION 100) in the place of  OPTION( MAXRECURSION 0) it give the following error

     

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

    Warning: Null value is eliminated by an aggregate or other SET operation.

     so how can resolve these issues  please any can suggest us as early as possible or give the solution

     

    thanks

    following is the my table , data and stored procedure

    /****** Object:  Table [dbo].[Table_DownLineList]    Script Date: 11/17/2013 21:22:04 ******/

    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', N'11-11-2013', 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')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (38, N'ANSAR', N'ANSAR KHAN', N'11-11-2013', N'RAJA1', 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 (40, N'ANSAR2', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (41, N'NASURUDDIN', N'MU. NASURUDDIN', N'11-11-2013', N'ANSAR2', N'ANSAR KHAN', N'MAINPURI', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (42, N'ANIS', N'ANIS KHAN', N'11-11-2013', N'NASURUDDIN', N'MU. NASURUDDIN', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (44, N'BASEEM', N'MU. BASEEM', N'11-11-2013', N'SURJEET01', N'SURJEET 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 (45, N'PREMPAL', N'PREMPALSINGH', N'11-11-2013', N'BASEEM', N'MU. BASEEM', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (47, N'GK1', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH KUMAR', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (49, N'AMLESH', N'AMALESH KUMAR', N'11-12-2013', N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (50, N'RAMESH', N'RAMESH CHANDRA', N'11-12-2013', N'AMLESH', N'AMALESH 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 (54, N'GOVIND2', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (55, N'DINESH', N'DINESH KUMAR', N'11-12-2013', N'GOVIND1', N'GOVIND SHAKYA', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (63, N'MANOJ1', N'MANOJ KUMAR', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (64, N'SHITAL', N'SHITAL DEVI', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (9, N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'11-10-2013', N'RAJKISHOR', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (10, N'RAJA', N'HARISH CHANDR', N'11-10-2013', N'rajkishor01', 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 (12, N'RAJA2', N'HARISH CHANDR', N'11-10-2013', N'RAJA', N'HARISH CHANDR', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (13, N'SURJEET', N'SURJEET SINGH', N'11-10-2013', N'RAJA2', N'HARISH CHANDR', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (17, N'PRADEEP', N'PRADEEP KUMAR', N'11-11-2013', N'RAMASARE', N'RAMASARE', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (20, N'DHARAMVEER', N'DHARMVEER', N'11-11-2013', N'JITENDR1', 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 (22, N'NEERAJA', N'NEERJA', N'11-11-2013', N'RAJKUMARI', N'RAJKUMARI', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (24, N'BRAJKISHOR', N'BRAJKISHOR', N'11-11-2013', N'RAMVEER', N'RAMVEER', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (29, N'RAJENDR', N'RAJENDR SINGH', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (35, N'MOHAN', N'MOHAN SINGH', N'11-11-2013', N'RAJKUMAR', N'RAJKUMAR', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (39, N'ANSAR1', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (51, N'GOPESH', N'GOPESH KUMAR', N'11-12-2013', N'RAMESH', N'RAMESH CHANDRA', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (53, N'GOVIND1', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (60, N'RAMVEER1', N'RAMVEER SINGH', N'11-12-2013', N'ROOPWATI', N'ROOPWATI', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (65, N'REKHA', N'REKHA KUMARI', N'11-12-2013', N'MANOJ1', N'MANOJ KUMAR', N'FIROZABAD', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (25, N'HARISWAROOP', N'HARISWAROOP', N'11-11-2013', N'SURJEET2', 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 (27, N'GEETA', N'GEETA  DEVI', N'11-11-2013', N'SATEESH', N'SATISH KUMAR', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (37, N'SAILENDRA', N'SAILENDR KUMAR', N'11-11-2013', N'RAVI', N'RAVI YADAV', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (52, N'GOVIND', N'GOVIND SHAKYA', N'11-12-2013', N'GK1', N'GIRJESH KUMAR', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (58, N'RAVI1', N'RAVI KUMAR', N'11-12-2013', N'NITIN', N'NITIN 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 (36, N'RAVI', N'RAVI YADAV', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (43, N'SURJEET01', N'SURJEET KUMAR', N'11-11-2013', N'ANIS', N'ANIS KHAN', N'ETAWAH', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (46, N'GK', N'GIRJESH KUMAR', N'11-11-2013', N'ANSAR1', N'ANSAR KHAN', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (48, N'GK2', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH 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 (59, N'ROOPWATI', N'ROOPWATI', N'11-12-2013', N'RAVI1', N'RAVI 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 (56, N'REKHA', N'REKHA', N'11-12-2013', N'DINESH', N'DINESH KUMAR', N'ETAWAH', N'Left')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (57, N'NITIN', N'NITIN KUMAR', N'11-12-2013', N'DINESH', N'DINESH 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 (61, N'KISHAN', N'KISHAN', N'11-12-2013', N'RAMVEER1', N'RAMVEER SINGH', N'NOIDA', N'Right')

    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (62, N'MANOJ', N'MANOJ KUMAR', N'11-12-2013', N'REKHA', N'REKHA', N'FIROZABAD', N'Left')

    SET IDENTITY_INSERT [dbo].[Table_DownLineList] OFF

     

    and following is the my stored procedure

     

    /****** Object:  StoredProcedure [dbo].[CountDownlineNode]    Script Date: 11/17/2013 21:21:55 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE 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 0)

    end

    GO

     

     



    Sunday, November 17, 2013 4:32 PM

Answers

  • It looks like there might be a circular relationship.  It causes the proc to go into an infinite loop.

    These member_ids occur twice: 

    34 MOHAN RAJKUMAR Right
    35 MOHAN RAJKUMAR Right
    56 REKHA DINESH Left
    65 REKHA MANOJ1 Left

    Remove the duplicate and I think that you might fix the problem with the data you have here.

    To avoid the error, try to add the following

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE proc [dbo].[CountDownlineNode]
    (
     @memberid nvarchar(50)
    )
    as
    begin
    ;with cte as (
            select
                 Member_Name,  Member_ID, Upline_ID, Placement_Leg,
                   null lnode,
                   null rnode,
                   1 as Level
            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
            , cte.Level + 1 as Level
            from Table_DownLineList t
            inner join cte
                    on cte.Member_ID=t.Upline_ID
            where cte.Level < 100
    )
    select
            @memberid Member_ID,
            SUM(lnode) LeftNodes,
            SUM(rnode) RightNodes
            from cte
    end
    GO
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, November 17, 2013 6:07 PM

All replies

  • I ran the below, which is just your script as I copied it from your post, excpet that I changed the MAXRECURSION hint for testing purposes, and it completed successfully.

    While I am at it. Your table design looks funny. All columns but Sr_no are nullable. What would an entry with only a value in, say, City, mean? Shouldnät at least Member_ID and Member_Name be NOT NULL? And shouldn'Member_ID be a key column

    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', N'11-11-2013', 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')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (38, N'ANSAR', N'ANSAR KHAN', N'11-11-2013', N'RAJA1', 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 (40, N'ANSAR2', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (41, N'NASURUDDIN', N'MU. NASURUDDIN', N'11-11-2013', N'ANSAR2', N'ANSAR KHAN', N'MAINPURI', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (42, N'ANIS', N'ANIS KHAN', N'11-11-2013', N'NASURUDDIN', N'MU. NASURUDDIN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (44, N'BASEEM', N'MU. BASEEM', N'11-11-2013', N'SURJEET01', N'SURJEET 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 (45, N'PREMPAL', N'PREMPALSINGH', N'11-11-2013', N'BASEEM', N'MU. BASEEM', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (47, N'GK1', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (49, N'AMLESH', N'AMALESH KUMAR', N'11-12-2013', N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (50, N'RAMESH', N'RAMESH CHANDRA', N'11-12-2013', N'AMLESH', N'AMALESH 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 (54, N'GOVIND2', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (55, N'DINESH', N'DINESH KUMAR', N'11-12-2013', N'GOVIND1', N'GOVIND SHAKYA', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (63, N'MANOJ1', N'MANOJ KUMAR', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (64, N'SHITAL', N'SHITAL DEVI', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (9, N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'11-10-2013', N'RAJKISHOR', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (10, N'RAJA', N'HARISH CHANDR', N'11-10-2013', N'rajkishor01', 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 (12, N'RAJA2', N'HARISH CHANDR', N'11-10-2013', N'RAJA', N'HARISH CHANDR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (13, N'SURJEET', N'SURJEET SINGH', N'11-10-2013', N'RAJA2', N'HARISH CHANDR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (17, N'PRADEEP', N'PRADEEP KUMAR', N'11-11-2013', N'RAMASARE', N'RAMASARE', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (20, N'DHARAMVEER', N'DHARMVEER', N'11-11-2013', N'JITENDR1', 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 (22, N'NEERAJA', N'NEERJA', N'11-11-2013', N'RAJKUMARI', N'RAJKUMARI', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (24, N'BRAJKISHOR', N'BRAJKISHOR', N'11-11-2013', N'RAMVEER', N'RAMVEER', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (29, N'RAJENDR', N'RAJENDR SINGH', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (35, N'MOHAN', N'MOHAN SINGH', N'11-11-2013', N'RAJKUMAR', N'RAJKUMAR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (39, N'ANSAR1', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (51, N'GOPESH', N'GOPESH KUMAR', N'11-12-2013', N'RAMESH', N'RAMESH CHANDRA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (53, N'GOVIND1', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (60, N'RAMVEER1', N'RAMVEER SINGH', N'11-12-2013', N'ROOPWATI', N'ROOPWATI', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (65, N'REKHA', N'REKHA KUMARI', N'11-12-2013', N'MANOJ1', N'MANOJ KUMAR', N'FIROZABAD', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (25, N'HARISWAROOP', N'HARISWAROOP', N'11-11-2013', N'SURJEET2', 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 (27, N'GEETA', N'GEETA DEVI', N'11-11-2013', N'SATEESH', N'SATISH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (37, N'SAILENDRA', N'SAILENDR KUMAR', N'11-11-2013', N'RAVI', N'RAVI YADAV', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (52, N'GOVIND', N'GOVIND SHAKYA', N'11-12-2013', N'GK1', N'GIRJESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (58, N'RAVI1', N'RAVI KUMAR', N'11-12-2013', N'NITIN', N'NITIN 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 (36, N'RAVI', N'RAVI YADAV', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (43, N'SURJEET01', N'SURJEET KUMAR', N'11-11-2013', N'ANIS', N'ANIS KHAN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (46, N'GK', N'GIRJESH KUMAR', N'11-11-2013', N'ANSAR1', N'ANSAR KHAN', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (48, N'GK2', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH 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 (59, N'ROOPWATI', N'ROOPWATI', N'11-12-2013', N'RAVI1', N'RAVI 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 (56, N'REKHA', N'REKHA', N'11-12-2013', N'DINESH', N'DINESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (57, N'NITIN', N'NITIN KUMAR', N'11-12-2013', N'DINESH', N'DINESH 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 (61, N'KISHAN', N'KISHAN', N'11-12-2013', N'RAMVEER1', N'RAMVEER SINGH', N'NOIDA', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (62, N'MANOJ', N'MANOJ KUMAR', N'11-12-2013', N'REKHA', N'REKHA', N'FIROZABAD', N'Left')
    SET IDENTITY_INSERT [dbo].[Table_DownLineList] OFF
     
    
     
    /****** Object:  StoredProcedure [dbo].[CountDownlineNode]   Script Date: 11/17/2013 21:21:55 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE 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 10)
    end
    GO
    exec [CountDownlineNode] 'LDS'
    go
    DROP PROCEDURE [CountDownlineNode]
    DROP TABLE [Table_DownLineList]
     
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, November 17, 2013 5:02 PM
  • That means that maximum recursion default level count (100) was exceeded. When you use OPTION (MAXRECURSION 0) you override the default recursion level.

    More often than not, this is due to some cyclic references in your code.

    Sunday, November 17, 2013 5:32 PM
  • It looks like there might be a circular relationship.  It causes the proc to go into an infinite loop.

    These member_ids occur twice: 

    34 MOHAN RAJKUMAR Right
    35 MOHAN RAJKUMAR Right
    56 REKHA DINESH Left
    65 REKHA MANOJ1 Left

    Remove the duplicate and I think that you might fix the problem with the data you have here.

    To avoid the error, try to add the following

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE proc [dbo].[CountDownlineNode]
    (
     @memberid nvarchar(50)
    )
    as
    begin
    ;with cte as (
            select
                 Member_Name,  Member_ID, Upline_ID, Placement_Leg,
                   null lnode,
                   null rnode,
                   1 as Level
            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
            , cte.Level + 1 as Level
            from Table_DownLineList t
            inner join cte
                    on cte.Member_ID=t.Upline_ID
            where cte.Level < 100
    )
    select
            @memberid Member_ID,
            SUM(lnode) LeftNodes,
            SUM(rnode) RightNodes
            from cte
    end
    GO
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, November 17, 2013 6:07 PM
  • As noted above in "normal" tree processing not likely you go 100 recursions deep.  Therefore you probably have a data issue.

    You can do deep recursion on purpose, see example:

    http://www.sqlusa.com/bestpractices2005/sequence/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, November 17, 2013 6:27 PM
    Moderator
  • >> following is the my table, data and stored procedure <<

    No, there is no table here. There is no key and the data is garbage filled with NULL-able columns. You do not know that rows are records are totally different concepts. You even prefixed a table name with “Table_” as if this was a 1950's tape file system! 

    Your ambiguous date format is not part of Standard SQL. We use ISO-8601 format yyyy-mm-dd as the only one allowed. We also use COALESCE() and not ISNULL() now. 

    IDENTITY is a physical table property that non-SQL programmers use to mimic record numbers used in magnetic tape files. 

    I also see that everything is fifty Chinese Unicode characters long! You did no research or planning did you? 

    My guess is that you are building 1950's pointer chains in SQL by trying to use IDENTITY values. This means that you have tried to put a relationship (the pointer chains) in the same table as the entities (members). However, you left off a constraint to prevent cycles and your code goes into an infinite loop. Oh, that constraint is a bitch to write and in 30 years, anything I have found destroys performance. 

    Let's go back to the basics. Members are entities so they have a table in a valid data model. 
     
    CREATE TABLE Membership
    (member_id CHAR(10) NOT NULL PRIMARY KEY, -- most ISO codes are 10 or less 
     member_name VARCHAR(35) NOT NULL,  -- postal standards 
     city_name VARCHAR(25) NOT NULL, -- wild guess 
     membership_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

    Now the relationship gets a table of its own! Again, I guess that what you want is a way to model a binary tree in SQL. Read

    https://www.simple-talk.com/sql/t-sql-programming/binary-trees-in-sql/ 

    CREATE TABLE Binary_Tree
    (member_id CHAR(10) NOT NULL
      REFERENCES Membership
      ON UPDATE CASCADE
      ON DELETE CASCADE,
     tree_location INTEGER NOT NULL PRIMARY KEY);

    I am not going to post the article, just read and implement the sections “Find the Number of Left (Right) Children in the Tree” and “Find Subtree Rooted at a Node” for what you seem to want to do. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, November 17, 2013 10:23 PM
  •  >>  Remove the duplicate and I think that you might fix the problem with the data you have here.<<

    This is not good advice. His schema is a pile of garbage and he needs to fix the problem, not kludge around it. Mop the floor, but you also have to fix the leak. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, November 17, 2013 10:27 PM
  • Dear friend , i am found duplicate record in my database table Table_DownLineList

    'REKHA'

    'MOHAN'

    after removing these duplicate record from my table the above code which was provide by you works fine for me

    Thanks

    Tuesday, November 19, 2013 5:54 AM
  • hi friend now i want to count number of pair in an mlm tree  2:1 or 1:2,1:1 ratio how it can be done please suggest us

    when we execute my stored procedure it give the following result.

    exec Countpair 'REKHA'

    Total_Pair

    ....................

        1

    ....................

    but if we follow the 2:1,or 1:2,1:1 ratio   then there is no any pair  so how it can be implement please suggest us

    following are the Binary tree image ,table,data, and stored procedure

    /****** Object:  Table [dbo].[Table_DownLineList]    Script Date: 11/19/2013 20:33:59 ******/
    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 (38, N'ANSAR', N'ANSAR KHAN', N'11-11-2013', N'RAJA1', 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 (40, N'ANSAR2', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (41, N'NASURUDDIN', N'MU. NASURUDDIN', N'11-11-2013', N'ANSAR2', N'ANSAR KHAN', N'MAINPURI', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (42, N'ANIS', N'ANIS KHAN', N'11-11-2013', N'NASURUDDIN', N'MU. NASURUDDIN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (44, N'BASEEM', N'MU. BASEEM', N'11-11-2013', N'SURJEET01', N'SURJEET 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 (45, N'PREMPAL', N'PREMPALSINGH', N'11-11-2013', N'BASEEM', N'MU. BASEEM', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (47, N'GK1', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (49, N'AMLESH', N'AMALESH KUMAR', N'11-12-2013', N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (50, N'RAMESH', N'RAMESH CHANDRA', N'11-12-2013', N'AMLESH', N'AMALESH 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 (54, N'GOVIND2', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (55, N'DINESH', N'DINESH KUMAR', N'11-12-2013', N'GOVIND1', N'GOVIND SHAKYA', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (63, N'MANOJ1', N'MANOJ KUMAR', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (64, N'SHITAL', N'SHITAL DEVI', N'11-12-2013', N'MANOJ', N'MANOJ KUMAR', N'FIROZABAD', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (66, N'SUMAN', N'SUMAN KUMAR RAJPUT', N'11-17-2013', N'LDS2', N'LDS', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (67, N'BRIJMOHAN', N'BRIJMOHAN', N'11-17-2013', N'GK2', N'GIRJESH 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 (68, N'PREMKISHOR', N'PREM KISHOR', N'11-17-2013', N'BRIJMOHAN', N'BRIJMOHAN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (72, N'AJAY', N'AJAY KUMAR', N'11-17-2013', N'VIKRAM', N'VIKRAM SONI', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (74, N'NEM', N'NEM SINGH SHAKYA', N'11-17-2013', N'BRIJMOHAN', N'BRIJMOHAN', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (76, N'RAMANAND', N'RAMANAND', N'11-17-2013', N'DURVIJAY', N'DURVIJAY 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 (78, N'GAURAV', N'GAURAV KUMAR', N'11-17-2013', N'NEM', N'NEM SINGH SHAKYA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (80, N'UMESHCHANDR', N'UMESH CHANDR', N'11-17-2013', N'SANJAY', N'SANJAY 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 (83, N'UMESH', N'UMESH SHAKYA', N'11-17-2013', N'HEMANT', N'HEMANT 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 (87, N'JITENDR001', N'JITENDR KUMAR', N'11-17-2013', N'ARVIND1', N'ARVIND KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (90, N'DARVESH1', N'DARVESH KUMAR', N'11-17-2013', N'DARVESH', N'DARVESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (94, N'PRAVENDRA', N'PRAVENDRA KUMAR', N'11-17-2013', N'SHILENDRA', N'SHILENDR 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 (96, N'VIJAYKUMAR', N'VIJAY KUMAR', N'11-17-2013', N'AMARJEET', N'AMARJET', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (98, N'RAMVEE2', N'RAMVEER SINGH', N'11-17-2013', N'AMARJEET', N'AMARJET', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (9, N'RAJKISHOR02', N'RAJKISHOR SHARMA', N'11-10-2013', N'RAJKISHOR', N'RAJKISHOR SHARMA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (10, N'RAJA', N'HARISH CHANDR', N'11-10-2013', N'rajkishor01', 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 (12, N'RAJA2', N'HARISH CHANDR', N'11-10-2013', N'RAJA', N'HARISH CHANDR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (13, N'SURJEET', N'SURJEET SINGH', N'11-10-2013', N'RAJA2', N'HARISH CHANDR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (17, N'PRADEEP', N'PRADEEP KUMAR', N'11-11-2013', N'RAMASARE', N'RAMASARE', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (20, N'DHARAMVEER', N'DHARMVEER', N'11-11-2013', N'JITENDR1', 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 (22, N'NEERAJA', N'NEERJA', N'11-11-2013', N'RAJKUMARI', N'RAJKUMARI', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (24, N'BRAJKISHOR', N'BRAJKISHOR', N'11-11-2013', N'RAMVEER', N'RAMVEER', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (29, N'RAJENDR', N'RAJENDR SINGH', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (35, N'MOHAN', N'MOHAN SINGH', N'11-11-2013', N'RAJKUMAR', N'RAJKUMAR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (39, N'ANSAR1', N'ANSAR KHAN', N'11-11-2013', N'ANSAR', N'ANSAR KHAN', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (51, N'GOPESH', N'GOPESH KUMAR', N'11-12-2013', N'RAMESH', N'RAMESH CHANDRA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (53, N'GOVIND1', N'GOVIND SHAKYA', N'11-12-2013', N'GOVIND', N'GOVIND SHAKYA', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (60, N'RAMVEER1', N'RAMVEER SINGH', N'11-12-2013', N'ROOPWATI', N'ROOPWATI', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (65, N'REKHA1', N'REKHA KUMARI', N'11-12-2013', N'MANOJ1', N'MANOJ KUMAR', N'FIROZABAD', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (69, N'SATENDRA', N'SATENDRA KUMAR', N'11-17-2013', N'PREMKISHOR', N'PREM KISHOR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (70, N'ATUL', N'ATUL KUMAR', N'11-17-2013', N'SATENDRA', N'SATENDRA 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 (82, N'HEMANT', N'HEMANT KUMAR', N'11-17-2013', N'BRAJESH', N'BRAJESH 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 (86, N'REENA', N'REENA SHAKYA', N'11-17-2013', N'ARVIND', N'ARVIND 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 (91, N'DARVESH2', N'DARVESH KUMAR', N'11-17-2013', N'DARVESH', N'DARVESH 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 (25, N'HARISWAROOP', N'HARISWAROOP', N'11-11-2013', N'SURJEET2', 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 (27, N'GEETA', N'GEETA  DEVI', N'11-11-2013', N'SATEESH', N'SATISH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (37, N'SAILENDRA', N'SAILENDR KUMAR', N'11-11-2013', N'RAVI', N'RAVI YADAV', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (52, N'GOVIND', N'GOVIND SHAKYA', N'11-12-2013', N'GK1', N'GIRJESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (58, N'RAVI1', N'RAVI KUMAR', N'11-12-2013', N'NITIN', N'NITIN 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 (77, N'SANJAY', N'SANJAY SINGH', N'11-17-2013', N'RAMANAND', N'RAMANAND', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (79, N'SHIV', N'SHIV KUMAR', N'11-17-2013', N'GAURAV', N'GAURAV 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 (36, N'RAVI', N'RAVI YADAV', N'11-11-2013', N'RAGHVENDR', N'RAGHVENDR 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 (43, N'SURJEET01', N'SURJEET KUMAR', N'11-11-2013', N'ANIS', N'ANIS KHAN', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (46, N'GK', N'GIRJESH KUMAR', N'11-11-2013', N'ANSAR1', N'ANSAR KHAN', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (48, N'GK2', N'GIRJESH KUMAR', N'11-11-2013', N'GK', N'GIRJESH 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 (59, N'ROOPWATI', N'ROOPWATI', N'11-12-2013', N'RAVI1', N'RAVI 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 (88, N'MAHESH1', N'MAHESH CHANDR', N'11-17-2013', N'REENA', N'REENA SHAKYA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (56, N'REKHA', N'REKHA', N'11-12-2013', N'DINESH', N'DINESH KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (81, N'BRAJESH', N'BRAJESH KUMAR', N'11-17-2013', N'SUMAN', N'SUMAN KUMAR RAJPUT', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (84, N'ARVIND', N'ARVIND KUMAR', N'11-17-2013', N'UMESH', N'UMESH SHAKYA', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (85, N'ARVIND1', N'ARVIND KUMAR', N'11-17-2013', N'ARVIND', N'ARVIND KUMAR', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (92, N'KRISHNA', N'KRISHNA KANT', N'11-17-2013', N'DARVESH2', N'DARVESH 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 (57, N'NITIN', N'NITIN KUMAR', N'11-12-2013', N'DINESH', N'DINESH 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 (61, N'KISHAN', N'KISHAN', N'11-12-2013', N'RAMVEER1', N'RAMVEER SINGH', N'NOIDA', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (62, N'MANOJ', N'MANOJ KUMAR', N'11-12-2013', N'REKHA', N'REKHA', N'FIROZABAD', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (71, N'VIKRAM', N'VIKRAM SONI', N'11-17-2013', N'ATUL', N'ATUL 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 (73, N'RAVIKANT', N'RAVI KANT', N'11-17-2013', N'AJAY', N'AJAY 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 (75, N'DURVIJAY', N'DURVIJAY SINGH', N'11-17-2013', N'NEM', N'NEM SINGH SHAKYA', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (89, N'DARVESH', N'DARVESH KUMAR', N'11-17-2013', N'MAHESH1', N'MAHESH CHANDR', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (93, N'SHILENDRA', N'SHILENDR KUMAR', N'11-17-2013', N'KRISHNA', N'KRISHNA KANT', N'ETAWAH', N'Right')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (95, N'AMARJEET', N'AMARJET', N'11-17-2013', N'KRISHNA', N'KRISHNA KANT', N'ETAWAH', N'Left')
    INSERT [dbo].[Table_DownLineList] ([Sr_No], [Member_ID], [Member_Name], [Joining_Date], [Upline_ID], [Upline_Name], [City], [Placement_Leg]) VALUES (97, N'SHRINARAYAN', N'SHRI NARAYAN', N'11-17-2013', N'VIJAYKUMAR', N'VIJAY KUMAR', N'ETAWAH', N'Left')
    SET IDENTITY_INSERT [dbo].[Table_DownLineList] OFF

    /****** Object:  StoredProcedure [dbo].[Count_pair]    Script Date: 11/19/2013 20:33:51 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    create proc [dbo].[Count_pair]
    (
     @Upline_ID as nvarchar(50)
    )
    as
    begin
    ;with Child as
    (
        select  Member_ID, Upline_ID from Table_DownLineList where  Member_ID=@Upline_ID
        union all
        Select Table_DownLineList.Member_ID,Table_DownLineList.Upline_ID from Table_DownLineList
        inner join Child
        on Table_DownLineList.Upline_ID= Child.Member_ID
    )
    select count(*) from (select c.Upline_ID from Child c where c.Upline_ID!=@Upline_ID
    group by c.Upline_ID
    having COUNT(c.Upline_ID)>1)a
    end
    GO

    Thanks





    Wednesday, November 20, 2013 4:57 AM