Get All Child Nodes Given A Parent

GetChildNodes.sql

DECLARE @TopID_in int -- The top level we want to resolve children for

SELECT @TopID_in = -- ID of parent node

;WITH HierarchyCTE(ID, ParentID) AS
(
	SELECT Id, ParentId
	FROM Categories
	WHERE Id = @TopID_in
	UNION ALL
	SELECT Categories.Id, Categories.ParentId
	FROM Categories
	INNER JOIN HierarchyCTE
		ON Categories.ParentId = HierarchyCTE.Id
)
SELECT *
FROM HierarchyCTE