Selecting records during recursive stored procedure

Question

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 that 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.

Answer 1

Hello,

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

Answer 2

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