I am trying to create a loop in stored procedure to forumulate a parent/child
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:

Categories
-------------------------------------------
1        Electronics        0
2        Computers        1
3        Cameras            1
4        Sony Cameras        3
5        Clothing        0
6        White TShirt        5

I would like the output to like the following

Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt


How can i get this to work via a stored procedure?

Something like this would work...

Drop Table #temp

Create Table #temp
    (
    CatID int,
    [Name] varchar(20),
    ParentID int
    )

insert into #temp values (1, 'Electronics',  0)
insert into #temp values (2, 'Computers',  1)
insert into #temp values (3, 'Cameras',  1)
insert into #temp values (4, 'Sony Cameras',  3)
insert into #temp values (5, 'Clothing',  0)
insert into #temp values (6, 'White TShirt',  5)

Drop Table #temp2

Create Table #temp2
    (
    CatID int,
    [Name] varchar(255),
    ParentID int
    )

Insert Into #temp2
Select CatID, [Name], ParentID
While @@Rowcount > 0
    Update    t
    Set    [Name] = t1.[Name] + ' -> ' + t.[Name],
        ParentID = t1.ParentID
    From    #temp2 t Inner Join #temp t1
    On    t.ParentID = t1.CatID

Select * From #temp2

HTH
Jason

Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.


ML

Loops!!??  Procedural code?? !! God! How non-relational!

Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Print statements not displaying
CURSOR question
Can I Simplify and Optimize This Long Query?
varchar converting into date
Multiple Inserts