SQL取得树型目录下的数据[通用存储过程]

--win 2kp, sqlserver 2000
--来自CSDN: libin_ftsafe(子陌红尘)
--生成测试数据
create table BOM(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(1000))
INSERT INTO BOM select 'D0','-1','服装'
INSERT INTO BOM select 'D1','D0','上衣'
INSERT INTO BOM select 'D2','D0','裤子'
INSERT INTO BOM select 'D3','D1','中山装'
INSERT INTO BOM select 'D4','D3','圆领中山装'
go

--创建存储过程
create procedure sp_getChile
@TName   nvarchar(40),
@IDName  nvarchar(40),
@PIDName nvarchar(40),
@Name    nvarchar(40),
@ID      nvarchar(20)
as
begin
    create table #T(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(40),Level INT)   
    declare @s nvarchar(4000)   
    set @s = N'declare @i int  set @i = 1'   
 set @s = @s + N'  
                    insert into #t select '+@IDName+N','+@PIDName+N','+@Name+N',@i from '+@TName+N' where ID='''+@ID+N''''
    set @s = @s + N'
    while @@rowcount<>0
    begin
        set @i = @i + 1       
        insert into #t
        select
            a.'+@IDName+N',a.'+@PIDName+N',a.'+@Name+N',@i
        from
            '+@TName+N' a,#t b
        where
            a.'+@PIDName+N'=b.'+@IDName+N' and b.Level = @i-1
    end'
    exec(@s)   
    select ID,ParentID,Name from #T
end
go

--执行存储过程
exec sp_getChile  'BOM','ID','ParentID','Name','D1'
/*
id         ParentID             name                
---------- -------------------- --------------------
D1         D0                   上衣
D3         D1                   中山装
D4         D3                   圆领中山装
*/

--删除测试数据
drop procedure sp_getChile
drop table BOM



转http://www.cnweblog.com/yionline/articles/48058.html