Recursion using CTE in SQL Server

USE [tempdb]

GO

      -- Creating Employee details table

        IF EXISTS(SELECT * FROM SysObjects WHERE name = 'EmpDetails' AND xtype = 'U')

BEGIN

       DROP TABLE [EmpDetails]

END

GO

       CREATE TABLE [EmpDetails]

              (EmpID VARCHAR (10),

               EmpName VARCHAR (20),

               ManagerID VARCHAR(10),

               PRIMARY KEY (EmpID))

GO

       INSERT INTO [EmpDetails] VALUES ('100','ARKA GUPTTA',NULL)

       INSERT INTO [EmpDetails] VALUES ('101','SAIKAT','100')

       INSERT INTO [EmpDetails] VALUES ('102','DEBRAJ','100')

       INSERT INTO [EmpDetails] VALUES ('103','ANIRBAN','100')

       INSERT INTO [EmpDetails] VALUES ('104','SUMAN','101')

       INSERT INTO [EmpDetails] VALUES ('105','SUPRIYO','102')

       INSERT INTO [EmpDetails] VALUES ('106','AMLAN','102')

       INSERT INTO [EmpDetails] VALUES ('107','UTPAL','102')

       INSERT INTO [EmpDetails] VALUES ('108','PONCHU','105')

       INSERT INTO [EmpDetails] VALUES ('109','DEBABRATA','105')

       INSERT INTO [EmpDetails] VALUES ('110','BITTU','107')

GO

       SELECT * FROM [EmpDetails]

GO

       -- Excute the below query to find the Manager of each Employee using Recursive CTE.

WITH

       cteReports (EmpID, EmpName, ManagerID, OrganizationLevel)

AS

(     

       -- Anchor Query

       SELECT A.EmpID, A.EmpName, A.ManagerID, 1

       FROM   dbo.[EmpDetails] A

       WHERE  A.ManagerID IS NULL

        --     Seperator

       UNION ALL

        -- Recursive Query  

       SELECT A.EmpID, A.EmpName, A.ManagerID, R.OrganizationLevel + 1

       FROM   dbo.[EmpDetails] A

              INNER JOIN cteReports R

              ON A.ManagerID = R.EmpID

)

       SELECT EmpID, EmpName, ManagerID,

              (SELECT EmpName FROM [EmpDetails] WHERE EmpID = cteReports.ManagerID) 'ManagerName', OrganizationLevel

       FROM   cteReports

              ORDER BY OrganizationLevel, ManagerID