I recently stumpled upon an interesting way to do recursive with CTE.
Links:
http://blog.sqlauthority.com/2008/07/29/sql-server-sql-server-simple-example-of-recursive-cte-part-2-maxrecursion-prevent-cte-infinite-loop/
http://blog.sqlauthority.com/2008/07/29/sql-server-sql-server-simple-example-of-recursive-cte-part-2-maxrecursion-prevent-cte-infinite-loop/
http://blog.pengoworks.com/index.cfm/2007/11/30/Finding-the-path-in-a-hierarchical-tree-in-MSSQL-2005
So the sql will look like this:
<code>
WITH CategoryTree AS
(
SELECT
CategoryId,
ParentCategoryId,
CONVERT(VARCHAR(MAX), theRow) AS thePath,
CONVERT(BIGINT, 1) AS prevNumer,
CONVERT(BIGINT, 0) AS prevDenom,
CONVERT(BIGINT, theRow) AS currNumer,
CONVERT(BIGINT, 1) AS currDenom
FROM
(
SELECT
CategoryId,
ParentCategoryId,
ROW_NUMBER() OVER (ORDER BY DisplayOrder) AS theRow
FROM [Nop_Category]
WHERE ParentCategoryId = 0
) y
UNION ALL
SELECT
y.CategoryId,
y.ParentCategoryId,
y.thePath + '' + CONVERT(VARCHAR(MAX), y.theRow) AS thePath,
prevNumer = y.currNumer,
prevDenom = y.currDenom,
(y.currNumer * y.theRow) + y.prevNumer AS currNumer,
(y.currDenom * y.theRow) + y.prevDenom AS currDenom
FROM
(
SELECT
e.CategoryId,
e.ParentCategoryId,
x.thePath,
x.currNumer,
x.currDenom,
x.prevNumer,
x.prevDenom,
ROW_NUMBER() OVER (ORDER BY e.DisplayOrder) AS therow
FROM CategoryTree x
JOIN [Nop_Category] e ON e.ParentCategoryId = x.CategoryId
) y
)
SELECT
CategoryId,
ParentCategoryId,
LEN(thePath)-1 as [Level]
/*
currNumer AS startNumer,
currDenom AS startDenom,
currNumer + prevNumer AS endNumer,
currDenom + prevDenom AS endDenom
*/
FROM CategoryTree ORDER BY thePath OPTION (MAXRECURSION 20)
</code>
It is very fast, and i'm sure it' beats the current implementation (currently it's being done in BL). Also a function like this would simplify methods where you want to display all products and categories greatly (you can just have one category repeater and one product repeater, no need for nesting....which is a pain in a repeater)