Recursive CTE with three tables

I'm using SQL Server 2008 R2 SP1. I would like to recursively find the first non-null manager for a certain organizational unit by "walking up the tree".

I have one table containing organizational units "ORG", one table containing parents for each org. unit in "ORG", lets call that table "ORG_PARENTS" and one table containing managers for each organizational unit, lets call that table "ORG_MANAGERS".

ORG has a column ORG_ID:

ORG_ID

1

2

3

ORG_PARENTS has two columns.

ORG_ID, ORG_PARENT

1, NULL

2, 1

3, 2

MANAGERS has two columns.

ORG_ID, MANAGER

1, John Doe

2, Jane Doe

3, NULL

I'm trying to create a recursive query that will find the first non-null manager for a certain organizational unit.

Basically if I do a query today for the manager for ORG_ID=3 I will get NULL.

SELECT MANAGER FROM ORG_MANAGERS WHERE ORG_ID = '3'

I want the query to use the ORG_PARENTS table to get the parent for ORG_ID=3, in this case get "2" and repeat the query against the ORG_MANAGERS table with ORG_ID=2 and return in this example "Jane Doe".

In case the query also returns NULL I want to repeat the process with the parent of ORG_ID=2, i.e. ORG_ID=1 and so on.

My CTE attempts so far have failed, one example is this:

WITH BOSS (MANAGER, ORG_ID, ORG_PARENT)
AS
( SELECT m.MANAGER, m.ORG_ID, p.ORG_PARENT
FROM dbo.MANAGERS m INNER JOIN
dbo.ORG_PARENTS p ON p.ORG_ID = m.ORG_ID
UNION ALL
SELECT m1.MANAGER, m1.ORG_ID, b.ORG_PARENT
FROM BOSS b
INNER JOIN dbo.MANAGERS m1 ON m1.ORG_ID = b.ORG_PARENT
)

SELECT * FROM BOSS WHERE ORG_ID = 3

It returns:

Msg 530, Level 16, State 1, Line 4 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

MANAGER ORG_ID  ORG_PARENT
NULL      3        2

You need to keep track of the original ID you start with. Try this:

DECLARE @ORG_PARENTS TABLE (ORG_ID INT, ORG_PARENT INT ) 
DECLARE @MANAGERS TABLE (ORG_ID INT, MANAGER VARCHAR(100))

INSERT @ORG_PARENTS (ORG_ID, ORG_PARENT)
VALUES  (1, NULL)
,       (2, 1)
,       (3, 2)

INSERT @MANAGERS (ORG_ID, MANAGER)
VALUES (1, 'John Doe')
,       (2, 'Jane Doe')
,       (3, NULL)
;
WITH BOSS
AS
( 
    SELECT      m.MANAGER, m.ORG_ID AS ORI, m.ORG_ID, p.ORG_PARENT, 1 cnt
    FROM        @MANAGERS m 
    INNER JOIN  @ORG_PARENTS p 
                ON p.ORG_ID = m.ORG_ID
    UNION ALL

    SELECT      m1.MANAGER, b.ORI, m1.ORG_ID, OP.ORG_PARENT, cnt +1
    FROM        BOSS b
    INNER JOIN  @ORG_PARENTS AS OP
            ON  OP.ORG_ID = b.ORG_PARENT
    INNER JOIN  @MANAGERS m1 
            ON  m1.ORG_ID = OP.ORG_ID 
)

SELECT  * 
FROM    BOSS 
WHERE   ORI = 3

Results in:

+----------+-----+--------+------------+-----+
| MANAGER  | ORI | ORG_ID | ORG_PARENT | cnt |
+----------+-----+--------+------------+-----+
| NULL     |   3 |      3 | 2          |   1 |
| Jane Doe |   3 |      2 | 1          |   2 |
| John Doe |   3 |      1 | NULL       |   3 |
+----------+-----+--------+------------+-----+

General tips:

Don't predefine the columns of a CTE; it's not necessary, and makes maintenance annoying.

With recursive CTE, always keep a counter, so you can limit the recursiveness, and you can keep track how deep you are.

edit:

By the way, if you want the first not null manager, you can do for example (there are many ways) this:

SELECT  BOSS.* 
FROM    BOSS 
INNER JOIN (
                SELECT  BOSS.ORI
                ,       MIN(BOSS.cnt) cnt
                FROM    BOSS
                WHERE   BOSS.MANAGER IS NOT NULL
                GROUP BY BOSS.ORI 
            ) X
        ON  X.ORI = BOSS.ORI
        AND X.cnt = BOSS.cnt
WHERE   BOSS.ORI IN (3)