Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( 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
---
| CATID | NAME | ParentId |
---
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
>From #temp
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.

Creating Nested Tables inside of a Existing SQL Server Express Table

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,

James Simpson

Straightway Technologies Inc.

The only way to "create a table that has a column that is a nested table", is to use an xml datatype.

Refer to Books Online, Topic: XML [SQL Server]

|||

As Arnie indicates, SQL Server doesn't support TABLE as a data type within a table. There is probably another way to accomlish what you're trying to do without going to XML, but it's hard to say without know what your goal is. In general, I'd suggest that you create a new table for your "nested" information and then use a 1:Many relationship between the two tables to map the nested info to the parent record.

Mike

Creating Nested Nodes

I am trying to write a stored procedure that will accept an XPATH to an element or an attribute, that is held in an SQL XML field, and then either updates or creates it setting its value to a @.value, also passed in.

Issue is the end point or the path to the end point may not entirely exist

For instance if i passed the following to the stored procedure, i would expect the node to be updated with the value.

xpath : /root/doc[@.ID=1]/Resource/Media[@.ID]

Value to be set : 10

I would assume that in order to set or update the node, that it would have to exist first. ?

Also, is it possible to create one XQuery that will check the path sent and then update or create it. ?

Thanks

Currently we do not allow variable parameters to be passed to the xml datatype .exist, .query() and .modify() methods. That means in order for you xpath to be parametric, you will have to construct dynamic queries. You can either do this on the client side or in a stored procedure that then calls sp_executesql. However, this opens you up to security risks (sql injection), so is not an ideal solution.