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

最新推荐文章于 2022-11-01 20:57:33 发布

乡野村夫卍宸昊

最新推荐文章于 2022-11-01 20:57:33 发布

阅读量543

点赞数

分类专栏： SQL Server 文章标签：存储 sql insert sqlserver table 测试

SQL Server 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

--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