Hi



I have a table of categories which look as follow:

CategoryId		int (key)

CategoryName	varchar(255)

CategoryParentId	int



The CategoryParentId is just referring back to a Category in the same table.

I'm cannot seem to get a procedure going where I can get the Tree breakdown

of this Categories.

What I'm looking for is something like this

Cars

	Sedan's

		Mercedes

			Blue

			Gray

		BMW

	Sport's

		Red

		Black

	4x4's



The levels that the Categories can down is unlimited. I need to display this

tree in a HTML page.

Please Help

Martin du Toit





NOTICE - This message contains privileged and confidential information

intended only for the use of the addressee named above. If you are not the

intended recipient of this message, you are hereby notified that you must

not disseminate, copy or take any action in reliance on it. If you have

received this message in error, please notify Candor Technologies

immediately. Any views expressed in this message are those of the individual

sender, except where the sender specifically states them to be the view of

Candor Technologies.

Try this - it's a solution taken from Ron Soukup's Inside SQL Server 7.0 (a

damn fine book). I haven't tested this, but get the book for an explanation.



Dave







DECLARE @level int, @current in



CREATE TABLE #stack

(depth_level int, cat_id int)



create table #chart

(seq_no int IDENTITY,

id_level int not null,

cat_id int not null)



set rowcount=1

select @level=1, @current=CategoryID from Categories where CategoryID

=ParentID

set rowcount=0



insert into #stack(depth_level,cat_id) values(@level, @current)



while (@level > 0)

begin

    if exists (select * from #stack where depth_level=@level)

    begin

        set rowcount=1

        select @current=CategoryID from #stack

        where depth_level=@level

        set rowcount=0



        insert into #chart(id_level, cat_id)

        select @level, @current



        delete from #stack

        where depth-level=@level and cat_id=@current



        insert into #stack

        select @level + 1, CategoryID

        from Categories

        where CategoryParentID = @current

        and CategoryParentID <> CategoryID



        if @@ROWCOUNT > 0

            select @level=@level+1



    end

else

    select @level=@level+1

end





-- output the results

select space((o.id_level-1)*5) + e.CategoryName

from #chart as o

join Categories as e on (e.CategoryID = o.cat_id)

order by o.seq_no



drop table #stack, #chart







"Martin du Toit" <martin@c...> wrote in message

news:21106@a..._databases...

>

> Hi

>

> I have a table of categories which look as follow:

> CategoryId int (key)

> CategoryName varchar(255)

> CategoryParentId int

>

> The CategoryParentId is just referring back to a Category in the same

table.

> I'm cannot seem to get a procedure going where I can get the Tree

breakdown

> of this Categories.

> What I'm looking for is something like this

> Cars

> Sedan's

> Mercedes

> Blue

> Gray

> BMW

> Sport's

> Red

> Black

> 4x4's

>

> The levels that the Categories can down is unlimited. I need to display

this

> tree in a HTML page.

> Please Help

> Martin du Toit

>

>

>

 In Oracle you can use hierarchical queries :



select CategoryId, categoryname level from table 

start with CategoryId= " & request("CategoryId")

connect by PRIOR CategoryId= CategoryParentId



in SQL server :

drop procedure sp_tree_personel

create procedure sp_tree_personel

	@level integer,

	@parentId integer

as

begin 

	declare @catIDinteger,

		@catnamevarchar(255),

		@catparent integer,

		@messageSpace varchar(255),

		@message varchar(255),

		@i integer,

		@newlevel integer



	/* increase level for recursion */

	select @newlevel = @level + 1



	/* set indentation */

	select @messageSpace = ' '

	select @i=0

	while @i < @level

	begin

		select @messageSpace = @messageSpace + ' '

		select @i = @i + 1

	end  



	/* get all tree elements for branch at given level */

	declare curTreedata cursor for

	select catid, catparent, catname	from category

	where parentid = @parentId



	open curTreedata

	FETCH NEXT FROM curTreedata INTO @catid, @catname, @catparent



	/* display tree items at this level and perform recursive call for

children */

	WHILE @@FETCH_STATUS = 0

	BEGIN

		SELECT @message = @messageSpace + rtrim(@janssennr) + '-' +

rtrim(@catname) + '-' + rtrim(@catparent)

		PRINT @message

		exec sp_tree_personel @newlevel, @catid

		FETCH NEXT FROM curTreedata INTO @catid, @familienaam,

@catname, @catparent

	END

  

	CLOSE curTreedata

	DEALLOCATE curTreedata

	

end





Generating the tree depends on the browser -> I'm doing the same thing at

the moment for IE4.01, but haven't found the best soultion (yet)



Greetingsm



Koen Van Tolhuyzen

Cronos Belgium





-----Original Message-----

From: Martin du Toit

To: ASP Databases

Sent: 24/11/00 7:13

Subject: [asp_databases] Recursive Query



Hi



I have a table of categories which look as follow:

CategoryId		int (key)

CategoryName	varchar(255)

CategoryParentId	int



The CategoryParentId is just referring back to a Category in the same

table.

I'm cannot seem to get a procedure going where I can get the Tree

breakdown

of this Categories.

What I'm looking for is something like this

Cars

	Sedan's

		Mercedes

			Blue

			Gray

		BMW

	Sport's

		Red

		Black

	4x4's



The levels that the Categories can down is unlimited. I need to display

this

tree in a HTML page.

Please Help

Martin du Toit





NOTICE - This message contains privileged and confidential information

intended only for the use of the addressee named above. If you are not

the

intended recipient of this message, you are hereby notified that you

must

not disseminate, copy or take any action in reliance on it. If you have

received this message in error, please notify Candor Technologies

immediately. Any views expressed in this message are those of the

individual

sender, except where the sender specifically states them to be the view

of

Candor Technologies.

Hi Koen



It seems to be the solution that I need, but I get the following errors:



Server: Msg 16915, Level 16, State 1, Line 0

A cursor with the name 'CAT_TREE_cursor' already exists.

Server: Msg 16905, Level 16, State 1, Line 0

The cursor is already open.



Obviously when you call the same SP from itself, it will try and open an

already open cursor.

Is there a way around this error.



Thanks

Martin