Storing a tree in a SQL table

Question

I have a table like the following,

Create Table Entries
(
    EntryID int,
    ParentEntryID int, // -1 if root node
    EntryText nvarchar(100)
)

I need to write a stored procedure which takes a leaf EntryID and returns the all entries through the root entry starts from that leaf entry. What is the best way of doing it?

My desired stored procedure would look something like the following,

CREATE PROCEDURE dbo.GetPath
    @leafEntryID int
AS     
    // what to do...
GO

I searched for it, and found two ways:

1. Using a temporary table and basically using a for loop insert new rows to it. (I'm not sure how exactly implement it.)
2. Using cursors. (I don't have much idea about this way, is it a better approach?)

PS: I'm using Microsoft SQL server 2008

Answer 1

The model you're using is called adjacency list and it requires a recursive query to deal with.

In SQL Server and several other engines this can be achieved through use of so called recursive CTE (common table expressions):

WITH    q AS
        (
        SELECT  *
        FROM    entries
        WHERE   entryId = @leafEntryId
        UNION ALL
        SELECT  e.*
        FROM    q
        JOIN    entries e
        ON      e.entryId = q.parentEntryId
        )
SELECT  *
FROM    q

Answer 2

You can read about different approaches of storing hierarchical data on StackOverflow Wiki, also I suggest you look to this MS SQL data type hierarchyId

And of course for adjacency list model in MS SQL you should use recursive CTE which wrote for you Quassnoi.