Return all results within a category including sub categories

I recently ran in to a situation where i had to return every article in my database that was under a particular category (including all articles within the sub categories, sub sub categories and so on). The Categories table relates a sub category to a category by a ParentID.

At first i went along the lines of a recursive sql statement but this caused problems when there was no articles in one of the top level categories (not an easy problem to spot). The solution i finally come up with was one i particularly like as it meant i did not have to modify my query much. I simply add the following function:

CREATE FUNCTION [dbo].[GetCategories]
(
@ParentID INT,
@Output VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
IF @Output = ”
BEGIN
SET @Output = Convert(VARCHAR(255), @ParentID)
ENDDECLARE @CategoryID INT
DECLARE c1 CURSOR FOR SELECT CategoryID FROM Categories WHERE ParentID = @ParentID
OPEN c1
FETCH NEXT FROM c1 INTO @CategoryID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Output = @Output + ', ' + Convert(VARCHAR(255), @CategoryID)
SET @Output = dbo.GetCategories(@CategoryID, @Output)
FETCH NEXT FROM c1 INTO @CategoryID
END
CLOSE c1
DEALLOCATE c1RETURN @Output
END

Now i could execute the following query:

SELECT * FROM Articles WHERE CategoryID IN dbo.GetCategories(1, '')

And it would return all the articles where the CategoryID is 1 or the category is a sub of the category specified.

Hope this helps.