Recursive CTE in SQL SERVER

Step1. Create Table


CREATE TABLE [dbo].[RegMast](
[RegID] [int] NOT NULL,
[RegNo] [varchar](12) NULL,
[RegName] [varchar](50) NULL,
[UplineRegID] [int] NULL,
[UplineLeg] [varchar](1) NULL,
[SponsorRegID] [int] NULL,
[IsActive] [bit] NULL,
[FirstName] [varchar](50) NULL,
[MIddleName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Password] [nchar](10) NULL,
[DateofJoin] [datetime] NULL,
[DateofBirth] [datetime] NULL,
[Occupation] [varchar](50) NULL,
[Qualification] [varchar](50) NULL,
[EmailID] [varchar](15) NULL,
[ContactNo] [varchar](10) NULL,
[Photo] [varchar](50) NULL,
[IsDelete] [bit] NULL,
[IsCreatedOn] [datetime] NULL,
[IsUpdatedOn] [datetime] NULL,
[IsDeletedOn] [datetime] NULL,
 CONSTRAINT [PK_RegMast] PRIMARY KEY CLUSTERED 
(
[RegID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 2.



Declare @RegID INT
SET @RegID = 2

;with cte as 
(

 select RegID,RegName,RegNO,UplineRegID,UplineLeg,1 AS Level
 from RegMast where RegID = @RegID
 UNION ALL 
 Select r.RegID,r.RegName,r.RegNO,r.UplineRegID,r.UplineLeg, Level + 1  
 from cte c INNER JOIN RegMast r ON c.RegID = r.UplineRegID

)

Select * from cte
Order by RegID