loop in stored procedure?

I have a situation where there is one table with parent and child nodes
i.e.
catID, ParentID
i would like to make a query to find out if an entry resides in a certain
category.
for instance, say i have
Root
--Category 1
--Child 1A
--Child 1B
--Child 1C
--Child 1Ca
if i know the CatID for the Child 1Ca node, how can i write a query that is
able to tell me that it's ultimtely a child of the root nde (Category 1)?
Keep in mind that Category 1 is not always the root node in the tree.Rob,
thanks for your help. this is exactly what i needed.
best wishes.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>