I came up with a method of retrieving all descendants of a 'category' today by joining into a custom function, and I thought I'd share it online.

Let's say your user can select up to 5 levels of categories in the search page, and you need to match all products that belong to any of the categories beneath that category. (I'm also going that we know that cursors are evil).

The method I'm presenting allows you to join the results of a function instead of doing a 'categoryid=X' WHERE statement, like so:

-- @CategoryId is a parameter for your query procedure

SELECT P.ProductId, P.Name
FROM Product P
INNER JOIN ProductCategory PC ON PC.ProductId = P.ProductId
INNER JOIN dbo.DescendantCategories(@CategoryId) CAT ON CAT.CategoryId = PC.CategoryId

Now, let's have a look at the DescendantCategories function:

CREATE FUNCTION dbo.DescendantCategories  
(  
 @CategoryId INTEGER /* Input Parameter */
)  
RETURNS @Output TABLE (CategoryId INTEGER) /* Return Table Definition */
AS  
BEGIN  
  
-- Ensure the given ID exists  
INSERT @Output(CategoryId)   
 SELECT CategoryId FROM Category WHERE CategoryId=@CategoryId  

-- While items were inserted
WHILE @@ROWCOUNT < 0  
BEGIN
 /* Add all children of categories already in the output table
    to the output table (provided they are not in there already) */
 INSERT @Output(CategoryId)
  SELECT CAT.CategoryId FROM @Output C
  INNER JOIN Category CAT ON CAT.ParentCategoryId = C.CategoryId
  LEFT JOIN @Output DUP ON DUP.CategoryId = CAT.CategoryId  
  WHERE DUP.CategoryId IS NULL /* Where its not in there already */
END  
  
RETURN  
  
END

So there it is; I hope you find good use for it. It's very fast and it even will circumvent infinite loops that could occur with circular hierarchy references.