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:
>

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:
> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> > I'm looking for different solution people have implemented to solve this
> > problem.
> >
> > Problem: People need to work remotely. While working remotely they want
> > to
> > gather information. This information might be survey info, or inspection
> > info, etc. This information is to be stored remotely on a handheld, or
> > laptop device. After they get back to the office/homeo office they want
> > to
> > upload the information to our corporate database, which is SQL Server
> > 2000.
> > This upload process should be very easy, like a like of a button, or done
> > automatically when the devices is docked into the corporate network. This
> > solution has to be supported over the Internet, and intranet.
> >
> > What I'm looking for is what technologies and processes have people put in
> > places to implement a solution to handle the problem I stated above.
> >
> > If you have more questions about what I am asking for please let me know.
> >
> >
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.
> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
> "Quentin Ran" wrote:
>> Replication is the MS SQL Server technology for this.
>> Quentin
>>
>> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
>> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>> > I'm looking for different solution people have implemented to solve
>> > this
>> > problem.
>> >
>> > Problem: People need to work remotely. While working remotely they
>> > want
>> > to
>> > gather information. This information might be survey info, or
>> > inspection
>> > info, etc. This information is to be stored remotely on a handheld, or
>> > laptop device. After they get back to the office/homeo office they
>> > want
>> > to
>> > upload the information to our corporate database, which is SQL Server
>> > 2000.
>> > This upload process should be very easy, like a like of a button, or
>> > done
>> > automatically when the devices is docked into the corporate network.
>> > This
>> > solution has to be supported over the Internet, and intranet.
>> >
>> > What I'm looking for is what technologies and processes have people put
>> > in
>> > places to implement a solution to handle the problem I stated above.
>> >
>> > If you have more questions about what I am asking for please let me
>> > know.
>> >
>> >
>>

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.
Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>
|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>
|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:
>