I have a standard hierarchical table as follows:
Id (Int, PK)
For top level categories, the ParentId = 0 rather than NULL.
I want to supply a category Id and return a set of rows which I can use in code to assemble an ordered list in the following format:
---- A 3.3
In this case, I want to supply the ID for A3.2.1 and retrieve its parent as well as any categories at the same level as the parent and so on. I only need a simple table of categories (they don't have to be in a particular order) as the structuring of the list will be done in separate code.
How can I do this with a stored procedure?