Get employee hierarchy using Sql query

alter function synprod.Get_RM_HIRE (@EMPID VARCHAR(50)) RETURNS TABLE AS RETURN WITH Emp_CTE AS ( SELECT userid,reporting_manager_id, (SELECT JOB_PROFILE_ID FROM dbo.DESIGNATION_DETAILS WHERE EMPLID=(SELECT EMPID FROM dbo.USERS WHERE USERID=reporting_manager_id))[GRADE] FROM dbo.GetEmpInformation WHERE empid=@EMPID UNION ALL SELECT e.userid,e.reporting_manager_id, (SELECT JOB_PROFILE_ID FROM dbo.DESIGNATION_DETAILS WHERE EMPLID=(SELECT EMPID FROM dbo.USERS WHERE USERID=E.reporting_manager_id))[GRADE] --(SELECT TOP 1 GRADE FROM dbo.Designation_Details WHERE userid=E.reporting_manager_id)[GRADE] FROM dbo.GetEmpInformation e INNER JOIN Emp_CTE ecte ON ecte.reporting_manager_id = e.userid and e.reporting_manager_id <> e.userid ) SELECT * FROM Emp_CTE GO /*********************************Descending Structure******************************/ WITH Emp_CTE AS ( SELECT userid,reporting_manager_id FROM dbo.GetEmpInformation WHERE empid='9999' UNION ALL SELECT e.userid,e.reporting_manager_id FROM dbo.GetEmpInformation e INNER JOIN Emp_CTE ecte ON ecte.userid=e.reporting_manager_id ) SELECT * FROM Emp_CTE GO /*********************************Descending Structure******************************/