applying a recursive function on a specific row in SQL Server

Question

im trying to combine these two ideas

1. recursive function - http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

2. SQL Call Stored Procedure for each Row without using a cursor

actually i have the two working individually

i can get a recursive function to work
and i can get a stored procedure to work on one row
but im having a hard time figuring out
how to get my recursive stored procedure to work on a selected row

i am also referencing this post:
sql recursive function - to find managers

so if i have a specific row

id    mngr_id    person_id
--    -------    ---------
12       1777          923

i want to count how many levels to the CEO which has a mngr_id = null
(as in one of the above examples)

the second part of this question:
if i wanted to do this on a random number of specific rows
would this be the efficient way to do it?

[post edited for clarification]
this code (adjusted for my sample data above)
which is from the above example 1 is excellent and works well

GO
WITH levels (mngr_id, person_id, id, Level)
AS
(
-- Anchor member definition
    SELECT e.mngr_id, e.person_id, e.id, 0 AS Level
    FROM MyEmployees AS e
    WHERE mngr_id IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.mngr_id, e.person_id, e.id, Level + 1
    FROM MyEmployees AS e
    INNER JOIN levels AS d
        ON e.mngr_id= d.person_id
)
-- Statement that executes the CTE
SELECT mngr_id, person_id, id, Level
FROM levels 
where id=@whatever_random_id_i_choose  -- <---- pseudocode
GO

note:
the reason why im posting is cuase my pseudocode doesnt work
and breaks when person_id is not unique

thank you all for your input

Answer 1

Your best bet would be to use a recursive common table expression. This way you can use it in a view too.

WITH ORG_STRUCTURE
AS (
    SELECT 
         id
        ,mngr_id
        ,person_id
        ,0 AS c_level
    FROM occupancies
    WHERE person_id = @target_user_id


    UNION ALL

    SELECT
          id
        ,mngr_id
        ,person_id
        ,ORG.c_level  + 1 AS c_level
    FROM ORG_STRUCTURE ORG
        INNER JOIN occupancies MAN
            ON ORG.manager_id = MAN.person_id

    -- prevents recursion errors if you have an incomplete org structure
    WHERE c_level < 100
)

SELECT
     id
    ,mngr_id
    ,person_id
    ,MAX(c_level)
FROM ORG_STRUCTURE
WHERE mngr_id IS NULL
GROUP BY id
    ,mngr_id
    ,person_id

A recursive CTX can only go 100 layers deep, so if you have an incomplete org structure, you need to add a throttle where clause to the second query (see comment).

The group by and MAX() may not be required if you have a well formed org structure. You can put this in a stored proc @target_user_id. If you want to only return the distance from CEO, you can limit the select from CTE to something like:

SELECT
    ,MAX(c_level)
FROM ORG_STRUCTURE
WHERE mngr_id IS NULL