How to do recursive select in SQL Server 2000

Suppose there's a table named [Category], which has 2 columns:
CategoryID int,
ParentCategoryID int

Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship.
Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?

You could write a single SELECT statement if you know the number of levels. But this will probably be hard to read and maintain. Best is to write a multi-statement table-valued function that takes the CategoryID as input parameter, gets each descendent until there is none and returns all the rows. This can be easily used in other SELECT statements. The use of multi-statement TVF however has some disadvantages since there will be no statistics for the results and the optimizer will just make a guess for the number of rows. This may produce bad execution plans depending on how you use such a TVF in your query.

If you upgrade to SQL Server 2005 then you can use a recursive common table expression (CTE) to write a single query that will produce the results. This is declarative in nature and may perform better than the TVF approach also. You may want to consider this option if you work with lot of hierarchical data.