1: DECLARE @PageID uniqueidentifier
2: CREATE TABLE #temp
3: (name VARCHAR(1024),
4: url VARCHAR(1024)
5: )
6:
7: DECLARE curs CURSOR FOR SELECT ID FROM sf_pagebase
8: OPEN curs
9: FETCH NEXT FROM curs INTO @PageID
10: WHILE @@FETCH_STATUS = 0
11: BEGIN
12: declare @url varchar(1024)
13: declare @ParentID uniqueidentifier
14: declare @MenuName varchar(1024)
15:
16: SELECT @MenuName = NULL, @url = NULL, @ParentID = null
17: select @url = '/' + Name, @ParentID = ParentID, @MenuName = MenuName from sf_pagebase pb
18: inner join sf_cmspagecontent cpc on pb.id = cpc.id
19: where pb.id = @PageID
20:
21: WHILE @ParentID IS NOT NULL
22: BEGIN
23:
24: SELECT @ParentID = ParentID, @url = '/' + Name + @url, @MenuName = MenuName + ' > ' + @MenuName from sf_pagebase pb
25: inner join sf_cmspagecontent cpc on pb.id = cpc.id WHERE pb.id = @ParentID
26: END
27: select @url = '~' + @url + '.html'
28: if (@MenuName IS NOT NULL)
29: INSERT INTO #temp VALUES(@MenuName, @url)
30:
31: FETCH NEXT FROM curs INTO @PageID
32: END
33:
34: CLOSE curs
35: DEALLOCATE curs
36:
37: SELECT * FROM #temp
38: order by url
39:
40: DROP TABLE #temp