Selecting records during recursive stored procedure

I've got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that's retrieved by a (rather large) series of queries...but I'm attempting to speed things up by using a recursive stored procedure.

(As I understanding, using CTE would be best of all. But I'm not sure there's a way of doing that if the ordering can vary from level to level. If I'm wrong, please do correct me.)

So, I've got it all working well...except that I can't figure out how to loop through the records to query the children of each category that I find. I've tried using a cursor but, again, it seems that ordering is an issue. (And again, correct me if I'm wrong.)

Here's the code (which is called from a parent procedure th开发者_JAVA技巧at creates the temp table):

BEGIN
SET NOCOUNT ON;

DECLARE @parent_new int, @custom_order_new varchar(1);
DECLARE @title varchar(255), @is_nav_hidden bit, @display int;

-- store the information about this category into the temp table
IF @parent > 0
BEGIN
    INSERT INTO #CategoryListTemp SELECT DISTINCT c.id, c2c.id_parent, c.title, cp.id, c.page_order, null, c.is_published, cu.is_album, u.firstname, u.lastname, c.is_nav_hidden from categories_categories c2c, categories c LEFT JOIN categories_users cu ON c.id=cu.id_category LEFT JOIN users u ON cu.id_user=u.id LEFT JOIN categories_pages cp ON c.id=cp.id_category AND cp.id_page=@id where c2c.id_category=c.id and c.id=@parent;
END

-- and then find out about its children
IF @custom_order='c' BEGIN
    SELECT DISTINCT @parent_new = c.id, @custom_order_new = c.page_order, @title=c.title, @is_nav_hidden=c.is_nav_hidden from categories_categories c2c, categories c where c2c.id_category=c.id and c2c.id_parent=@parent order by c.is_nav_hidden, c.title;
END
ELSE
BEGIN
    SELECT DISTINCT @parent_new = c.id, @custom_order_new = c.page_order, @is_nav_hidden=c.is_nav_hidden, @display=c2c.display, @title=c.title from categories_categories c2c, categories c where c2c.id_category=c.id and c2c.id_parent=@parent order by c.is_nav_hidden, c2c.display, c.title;
END

if @parent_new IS NOT NULL BEGIN
    EXEC GetCategoryListData @id, @parent_new, @custom_order_new
END
END

Any pointers would be very gratefully received. Many thanks.

I'm just starting out on StackOverflow and looking for questions to answer. Yours looks interesting, BUT it would be a lot of work to actually create the sample records necessary to develop and test an answer.

A suggestion - use the link below (How to post data/code on a forum to get the best help).

http://www.sqlservercentral.com/articles/Best+Practices/61537/

It'll give you hints on creating sample data scripts to make your problem easy to replicate hopefully solve.

Sylvia

You need recursive CTE for your task if I have understand ur question. Look into this Common Table Expressions (CTE) in SQL Server 2005 . A more simplified example is here SQL SERVER – Simple Example of Recursive CTE