Hints are not allowed on recursive common table expression (CTE) references. Consider removing hint from recursive CTE reference 'CTE'

Question

I have one Employee table here is table structure

Name    varchar
GUID    numeric
ParentGUID  numeric

here is some sample data

NAME GUID ParentGUID
ABC    1   NULL
BCD    2   1
xyz    3   2
PQR    4   2
MRS    5   3

This table contains big hierarchy of Employee and manager. I need to pick all the Employee coming under particular employee. Ex. I need all the Employees coming under BCD, so result should be

 xyz    3   2
 PQR    4   2

here is my recursive query for that.

;WITH CTE (Name, GUID, ParentGUID)
    AS
    (
    select distinct B.Name , B.GUID,  B.ParentGUID
    FROM 
    EMP B with (nolock)     

    union All

    select a.Name , a.GUID, a.ParentGUID
    FROM EMP a with (nolock)
    inner join CTE C with (nolock)  on a.ParentGUID = c.GUID
    )
    select *
    FROM CTE B with (nolock)     where B.Name in ('BCD')

But it's giving me error.

Msg 4150, Level 16, State 1, Line 1
Hints are not allowed on recursive common table expression (CTE) references. Consider removing hint from recursive CTE reference 'CTE'.

Can you anyone please help me to correct this query.

Answer 1

Your where B.Name in ('BCD') is what is filtering your result set to just the one row. Change it to the below and you should get the results you want:

;with cte (Name, GUID, ParentGUID)
    as
    (
    select distinct B.Name
                   ,B.GUID
                   ,B.ParentGUID
    from EMP B
    where B.Name in ('BCD')

    union All

    select a.Name
          ,a.GUID
          ,a.ParentGUID
    from EMP a
        inner join CTE C
            on a.ParentGUID = c.GUID
    )
    select *
    from CTE