Recursive function in sql server

I don't quite understand the data in your example. The TopParent table includes numbers 5, 6, and 7, which I took to mean that they were top-level parents, but those numbers also appear as children in ParentChild. Also, number 7 is shown as having multiple parents in the ParentChild table, and while both of the parents have the same ultimate parent in this particular case, the fact that multiple parents are possible suggests the possibility of cases where a record has multiple top-level parents.

That said, if you're using SQL Server 2005 or later, you can use common table expressions to write recursive queries. I've writte a simple example which is hopefully close enough to your case that you'll be able to adapt it. The major differences are that each child has only one parent, and I've used a single table which uses NULL in the parent_id column to indicate a top-level parent.

-- Here's some basic sample data similar to your ParentChild table.
declare @parentChild table (parent_id int, child_id int);
insert into @parentChild values
    (null, 1), (null, 2), (1, 3), (1, 4), (2, 5), (4, 6), (5, 7), (6, 8);

-- A common table expression allows you to write recursive queries.
with TopParentCTE as
(
    -- The base case: select those records which have no immediate parent.
    select child_id, parent_id
    from @parentChild
    where parent_id is null

    union all

    -- The recursive case: for records which have a parent, join to the CTE
    -- to get the next-highest parent. The recursion ends when this case no
    -- longer returns any results.
    select C.child_id, isnull(P.parent_id, P.child_id)
    from @parentChild C
        inner join TopParentCTE P on C.parent_id = P.child_id
)
select
    PC.child_id as [Child],
    PC.parent_id as [Immediate Parent],
    TP.parent_id as [Ultimate Parent]
from
    @parentChild PC
    inner join TopParentCTE TP on PC.child_id = TP.child_id
order by
    PC.child_id;