sql server - Get all employees under manager with CTE -

i have table has employee details

empid managerid level value 1      0         5     ceo 2      1         4     emp 3      1         4     org 4      2         3     null 5      2         3     null 6      2         2     null 7      1         1     null 8      5         0     null 

now, have start wil employee id 2 , found it's low level hirerachy (i.e. 2, 4, 5, 6, 8) , assign them value same "2" (i.e. emp).

expected output :

  empid managerid level value     1      0         5     ceo     2      1         4     emp     3      1         4     org     4      2         3     emp     5      2         3     emp     6      2         2     emp     7      1         1     null     8      5         0     emp 

what trying:

    ; levelhire (         select empid, managerid,level         employeetable         empid =2         union         select lh.empid,  rc.managerid, lh.level         levelhire lh         inner join [employeetable] rc         on lh.empid= rc.empid  )  select * levelhire option (maxrecursion 0) 

how can achieve same?

you can try this

;with employeetable  ( select 1 empid,0 managerid ,   5 level ,'ceo' value union select 2,1,   4,'emp' union select 3,1,   4,'org' union select 4,2,   3,null union select 5,2,   3,null union select 6,2,   2,null union select 7,1,   1,null union select 8,5,   0,null ),levelhire (         select empid, managerid,level,value         employeetable         empid = 2         union         select rc.empid,  rc.managerid, lh.level,lh.value         levelhire lh         inner join [employeetable] rc         on lh.empid= rc.managerid ) select  e.empid, e.managerid,e.level,isnull(e.value ,lh.value) value employeetable e     left join levelhire lh     on e.empid = lh.empid