CREATE PROCEDURE select_components_by_conveyor_type @CategoryID bigint
AS
BEGIN
SET NOCOUNT ON
DECLARE @reached TABLE (category bigint, subcategory bigint NOT NULL, UNIQUE(category, subcategory))
INSERT INTO @reached VALUES (NULL, @CategoryID)
WHILE( @@rowcount > 0) BEGIN
INSERT INTO @reached (category, subcategory)
SELECT DISTINCT Categories.ParentID, Categories.CategoryID FROM Categories
JOIN @reached AS r ON r.subcategory = Categories.ParentID WHERE Categories.CategoryID NOT IN (SELECT subcategory FROM
@reached WHERE Category = Categories.ParentID)
END
SELECT Components.ComponentID, Components.Component, Components.EnglishDescription, Components.GermanDescription, Components.AdditionalInfo
FROM Components JOIN CategoriesByComponentType ON Components.ComponentID = CategoriesByComponentType.ComponentID
JOIN @reached AS r ON CategoriesByComponentType.CategoryID= r.subcategory ORDER BY Components.Component
SET NOCOUNT OFF
END
GO