The following code can be inserted in a SQL Server database as a User Defined Function (UDF). A UDF behaves much like a stored procedure with the difference that a UDF can be queried like a table and taken like a part of the WHERE-clause. The function is written specifically for the Livelink DTREE table in which all the Livelink objects are stored. Each of these objects have a reference to its parent. We felt the need to be able to write a Livereport about a folder object and all the objects under it in multiple levels. This function returns a table with all the DataID's directly or indirectly referencing to a parent object such as a folder, task list or compound document.

Please note that (1) a UDF is a SQL Server 2000 specific functionality. And (2) that Oracle has its own SQL statements to traverse through a hierarchical table like the Livelink DTREE table ("START WITH" and "CONNECT BY"). At last, you will notice that this approach is not very efficient for you database since, depending on the depth of the folder object, a lot of SELECT and INSERT are being executed. Use this function with caution and do not let users select the folder object theirselves. Consider this function as an example to do it and use it on your own risk.

'--- Create the function ("CREATE"), define what it will return ("RETURNS") and what it will do to get the
'--- result ("AS", "BEGIN"/"END")
CREATE FUNCTION AFM_getChildren(@IncludeParent bit, @DataID int)
RETURNS @retFindChildren TABLE (DataID int, ParentID int)
AS
BEGIN
    '--- If the IncludeParent parameter is set to 1 the first entry of the table is filled with the
    '--- information about the object that is queried. In most cases rather useless, unless you need to
    '--- know the parent object for all objects, including the start object, in your query.
    IF (@IncludeParent=1)
    BEGIN
        INSERT INTO @retFindChildren SELECT DataID, ParentID FROM dtree WHERE DataID=@DataID
    END
    
    '--- Declare temporary variables and temporary table and open the table for writing.
    DECLARE @ChildID int, @ChildsParentID int
    DECLARE RetrieveChildren CURSOR STATIC LOCAL FOR SELECT DataID, ParentID FROM dtree WHERE ParentID=@DataID
    OPEN RetrieveChildren
    
    '--- Loop through the DTREE table for every object found as a child of the previously found objects,
    '--- insert them all in the temporary table with the newly found result. Continue the loop until
    '--- nothing more is found.
    FETCH NEXT FROM RetrieveChildren INTO @ChildID, @ChildsParentID
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        INSERT INTO @retFindChildren SELECT * FROM AFM_GetChildren(0,@ChildID)
        INSERT INTO @retFindChildren VALUES(@ChildID, @ChildsParentID)
        FETCH NEXT FROM RetrieveChildren INTO @ChildID, @ChildsParentID
    END
    
    '--- Close and release the temporary table
    CLOSE RetrieveChildren
    DEALLOCATE RetrieveChildren
    
    RETURN
END

Thanks to Koen Bonnet for posting this.