高效加载数据库树（原创）（备忘）

View Code 
 1 DECLARE @stime DATETIME 
 2 SET @stime = GetDate()
 3 
 4 DECLARE @returnValue TABLE
 5     (
 6       id INT ,
 7       parentId INT ,
 8       [path] VARCHAR(1000) ,
 9       lev INT
10     )
11 
12 DECLARE @i INT
13 SET @i = 0
14 
15 INSERT  INTO @returnValue
16         SELECT  ID ,
17                 ParentID ,
18                 CONVERT(VARCHAR(50), ID) ,
19                 @i
20         FROM    iArticleCatalog
21         WHERE   ParentID IS NULL
22                 OR ParentID = 0 
23 WHILE @@rowcount > 0 
24     BEGIN
25         SET @i = @i + 1
26         INSERT  INTO @returnValue
27                 SELECT  b.id ,
28                         b.parentId ,
29                         a.[path] + '/' + CONVERT(VARCHAR(50), b.ID) ,
30                         @i
31                 FROM    @returnValue a ,
32                         iArticleCatalog b
33                 WHERE   a.id = b.parentid
34                         AND a.lev = @i - 1
35     END
36 
37 --SELECT * FROM @returnValue     
38 PRINT DATEDIFF(ms,@stime,getdate())

发贴备忘

posted @ 2011-12-14 14:42  _丑牛  阅读(148)  评论(0)  编辑  收藏  举报