Recursive query in sqlserver 2008

--CBY-SANJAY KUMAR ACHARYA
--CDATE-04 OCTOBER,2012
--exec SP_BI_FetchSubhandleby 12051
create procedure SP_BI_FetchSubhandleby
@Handleby int
as
BEGIN
    WITH SubhandleBy (em_emid, em_name,em_rprto,ds_desc)
        AS
        (           
            SELECT a.em_emid,a.em_name,a.em_rprto,b.ds_desc
            FROM EMPMAST a           
            INNER JOIN DESGMAST b ON b.ds_dsid=a.em_dsid
            WHERE a.em_emid=@Handleby AND a.em_status<>'D'
            UNION ALL
            -- Recursive member definition
            SELECT a.em_emid,a.em_name,a.em_rprto,b.ds_desc
            FROM EMPMAST a
            INNER JOIN DESGMAST b ON b.ds_dsid=a.em_dsid
            INNER JOIN SubhandleBy AS SB
            ON a.em_rprto = SB.em_emid
            WHERE a.em_status<>'D' AND a.em_emid<>a.em_rprto
        )
   
        SELECT DISTINCT
        em_emid EmployeeId,
        em_name+' ('+ CONVERT(VARCHAR,em_emid)+')',
        em_rprto ReportTo
        FROM
        SubhandleBy WHERE em_emid NOT IN(1,2,3)
        ORDER BY em_rprto
END