December 5, 2006 at 10:39 pm
[RESOLVED NOW, THANKS]
Hey guyz,
I have a table which stores some information about a website map and the data looks something similar to below.
WebPageId | ParentPageId | Name
---------------------------------------
420 | 9 | level_3
9 | 2 | level_2
2 | 1 | level_1
1 | NULL | root
Basically I want to make a stored procedure where I pass the webpage name and it should give me the root WebPageId where the ParentPageId IS NULL.
Could anyone please help me?
WRACK
CodeLake
December 5, 2006 at 11:46 pm
take a look at this. u will need to make some changes as per your requirements
declare
@name varchar(100)
declare
@parentpageid int
declare
@webpageid int
set
@name = 'level_3'
set
@parentpageid = 0
set
@webpageid = 0
SELECT
@webpageid = Webpageid from test WHERE WName = @name
While
ISNULL(@parentpageid,1) <> 1
BEGIN
SELECT
@parentpageid = parentpageid, @webpageid = @webpageid from test
WHERE
Webpageid = @webpageid
IF
ISNULL(@parentpageid,0) <> 0
BEGIN
SET @webpageid = @parentpageid
END
select
@parentpageid,@webpageid
END
"Keep Trying"
December 6, 2006 at 3:39 pm
OK with minor changes it's working now.
DECLARE @WebPageName VARCHAR(100) DECLARE @ParentPageId INT DECLARE @WebPageId INT -- SET @WebPageName = 'aus_young_matildas_news_feat_news_item' SET @ParentPageId = 0 SET @WebPageId = 0 -- SELECT @WebPageId = Webpageid FROM WebPage WHERE Name = @WebPageName -- WHILE @ParentPageId IS NOT NULL BEGIN SELECT @ParentPageId = ParentPageId, @WebPageId = @WebPageId FROM WebPage WHERE Webpageid = @WebPageId -- IF @ParentPageId IS NOT NULL BEGIN SET @WebPageId = @ParentPageId END END -- SELECT @WebPageId AS RootWebPageId
WRACK
CodeLake
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply