May 11, 2006 at 7:43 pm
Hi,
I have a table called PageLinks structured as follows:
PageID - PK
ParentID - PK
Path - string
TemplateID - FK
The table is linked to another called Pages with a PK of PageID that links to the PageID in this table and ParentID to provide a heirachical data structure.
The structure allows a single page to have one or more parents so in order to keep track of where a page is in the hierachy I use the Path field to store the position in the tree by storing the PageIDs of the parent up the line in the following format 1-29-48-72. In the actual app it is this information that is passed around as the querystring and is therefore indexed to speed the searches.
I want to be able to write a stored procedure that will take that string, and then check each record in the branches leading to the root to see if a template has been applied by checking to see if TemplateID is not null.
So in other words the stored procedure needs to be able to accept the string and then search each entry PageLinks table for a TemplateID in the following order:
1-29-48-72
1-29-48
1-29
1
Any pointers with this would be appreciated.
Cheers,
Julian
May 12, 2006 at 1:11 am
declare @string varchar(50)
select @string = '1-29-48-72'
----------------------- real code here
declare @index int, @S varchar(50)
select @S = @string, @index=1
while @index > 0
begin
print @S
<search table for @S>
select @index = charindex('-', reverse(@s))
select @S = left(@s, len(@s) - @index)
end
-----------------------
N 56°04'39.16"
E 12°55'05.25"
May 12, 2006 at 2:26 am
Hi Peter, thanks for that.
Just one final thing, how do I structure the table search so that when I come across a TemplateID the loop stops and returns the TemplateID?
Cheers,
Julian
May 12, 2006 at 3:25 am
Actually don't worry about it, have managed to work it out myself.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply