Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-26 : 12:57:44
|
Mac writes "I've a table like this
/***************************************************/
CREATE TABLE [Category_T] (
[idCategory] [int] IDENTITY (1, 1) NOT NULL ,
[vcName] [varchar] (50) NOT NULL ,
[idCategoryP] [int] NOT NULL
)
/***************************************************/
where idCategoryP is the parent Category'id ID.
Now, let's say that I want to retrieve all the child node of the cateogory (id is 2), how do I do that?
I created the following storeproc but do not know how to assemble the result set and return them as a whole.
/***************************************************/
CREATE PROCEDURE sp_GetAllChildnode
(
@idCategory INT
)
AS
DECLARE @return_id INT
SELECT @return_id = idCategory
FROM category_T
WHERE idcategoryP = @idcategory
IF @@ROWCOUNT > 0
EXECUTE sp_GetAllChildnode @return_id
/***************************************************/
My current partial solution is to construct a hard-code inefficient UNION storeproc (which only return children within 4 levels)
/***************************************************/
CREATE PROCEDURE sp_getallchildnode
(@idCategory int)
AS
(
SELECT idCategory AS idC
FROM category_T
WHERE idcategoryP = @idcategory
)
UNION
(
SELECT T2.idCategory AS idC
FROM category_T AS T1
JOIN category_T AS T2 ON
T2.idcategoryP = T1.idcategory
WHERE T1.idcategoryP = @idcategory
)
UNION
(
SELECT T3.idCategory AS idC
FROM category_T AS T1
JOIN category_T AS T2 ON
T2.idcategoryP = T1.idcategory
JOIN category_T AS T3 ON
T3.idcategoryP = T2.idcategory
WHERE T1.idcategoryP = @idcategory
)
UNION
(
SELECT T4.idCategory AS idC
FROM category_T AS T1
JOIN category_T AS T2 ON
T2.idcategoryP = T1.idcategory
JOIN category_T AS T3 ON
T3.idcategoryP = T2.idcategory
JOIN category_T AS T4 ON
T4.idcategoryP = T3.idcategory
WHERE T1.idcategoryP = @idcategory
)
THANKS!!
-- Mac
" |
|
|
|
|
|
Recursive Stored Proc ??