Multilevel nested product categories display with asp.net and sql server?

Question

I have a product category table with the following fields:

cat_id (PK)

Cat_name

Cat_desc

Parent_Cat_Id

Now when a user wants to add a product he should be able to select multiple categories from a listbox(multiselection enabaled). But to let the user know the hierarchy of the categories, I need to display them in the following style:

parent category 1

parent category 1->sub category 1

parent category 1->sub category 1->sub-sub category 1

parent category 1->sub category 1->sub-sub category 2

parent category 1->sub category 2

Parent category 2

...

I know that to achieve this I need to use recursive programming. But how can we do it via stored procedures? That would be much more efficient, right?

Thanks in advance for your kind help.

Answer 1

Are you using Linq?

If you use LINQ i´d suggest adding a new property which just represents the category with all parentcategorys and use the recursion there, so it´s easy for you to access it in asp.net

Answer 2

Here's what you want. This stored procedure uses a "CTE" or "common table expression". I'm not a pro at them but they're very usefull. Definatly recommend looking them up.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetRecursiveTest
AS
BEGIN
    ;WITh Tree (cat_id, cat_name, parent_cat_id, level, Sort) AS
    (
        SELECT cat_id, cat_name, parent_cat_id, 0 AS level, 
            CONVERT(varchar(255), cat_name) AS Sort 
        FROM RecursiveTest
        WHERE parent_cat_id = 0

        UNION ALL

    SELECT RT.cat_id, RT.cat_name, RT.parent_cat_id, Parent.level + 1 AS level, 
        CONVERT(varchar(255), Parent.Sort + ' -> ' + RT.cat_name) AS Sort
        FROM RecursiveTest RT
        INNER JOIN Tree as Parent ON Parent.cat_id = RT.parent_cat_id
    )

    SELECT Sort FROM Tree
    ORDER BY Sort
END
GO

Actually, if you change that last select to "selct * from tree" you'll get more data about the hierarchy.

Here's a link to a good resource for CTE's