Table:
Create Table Category (
Id int,
Name int,
Parentid int
)
Recursive upwards:
Code
Create Function [DBO]. [getrootcategoryid]
/*
Author: yanghh
Date: 2009-08-10
Des: obtains a type of root node.
*/
(
@ Categoryid int
)
Returns @ subcategory table (categoryid int, level INT)
As
Begin
Declare @ level int
Set @ level = 0
Insert into @ subcategory
Select ID, @ level from category where id = @ categoryid
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ subcategory
Select parentid, @ level from Category A, @ subcategory B
Where B. categoryid = A. ID and B. Level = @ level-1
End
Return
End
Recursive downward:
Code
Create Function [DBO]. [getchildcategoryid]
/*
Author: yanghh
Date: 2009-08-10
Des: obtains a class of special subnodes.
*/
(
@ Categoryid int
)
Returns @ subcategory table (categoryid int, level INT)
As
Begin
Declare @ level int
Set @ level = 0
Insert into @ subcategory
Select ID, @ level from cc_vod_filmcategory where id = @ categoryid
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ subcategory
Select parentid, @ level from Category A, @ subcategory B
Where B. categoryid = A. parentid and B. Level = @ level-1
End
Return
End