Ancestor and Descendant IDs/info list using Common Table Expressions

Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write it from the top of my head, sometimes you just want to make life easier and have a script you can just modify a bit to fit the new requirement.

So what I usually use for recursions in SQL Server 2005 are Common Table Expressions (the "WITH" keyword). This is only available in SQL 2005 and very easy/efficient for recursive.

This is definitely in BOL and articles all over nevertheless here's my basic script to get "Descendants and Self" and "Ancestors and Self" information. In this case I only involved the ID, ParentID, and Sequence but you may add more columns (ID and ParentID are required to work). Also you might want to just retrieve the ID and just perform a JOIN afterwards.

Please read full article from .NET Developer Notes on Ancestor and Descendant IDs/info using Common Table Expressions