--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
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 function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(100),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,ParentID,Name,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,a.Name,@i
from
BOM a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID,ParentID,Name from dbo.f_getChild('D1') order by Level
go--输出结果
/*
ID ParentID Name
---- --------- ----------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
*/--删除测试数据
drop function f_getChild
drop table BOM
[create] table t
(id varchar(10),ParentID varchar(20),name varchar(20))
insert t values ('D0','-1','服装')
insert t values ('D1','D0','上衣')
insert t values ('D2','D0','裤子')
insert t values ('D3','D1','中山装')
insert t values ('D4','D3','圆领中山装')
go
create function f_gui(@col1 varchar(20))
returns @t table(id varchar(10),ParentID varchar(20),name varchar(20),level int)
as
begin
declare @i int
set @i=1
insert into @t select *,@i from t where id=@col1
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a,@t b
where a.ParentID=b.id and b.level=@i-1
end
return
end
go/*--------查询语句-------*/
select id,ParentID,name from dbo.f_gui('D1')drop function f_gui
drop table t /*-----------结果----------*/
id ParentID name
---------- -------------------- --------------------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装(所影响的行数为 3 行)
要详细写出来太长了
如果的参数是 表名,ID字段名,ParentId字段名,Name字段名, 节点的值当我 exec p_getChile 'myTree','ID','parentID','Name','D1'后得到一个下面的数据
id ParentID name
---------- -------------------- --------------------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
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
--------------------------------------------------------
函数内部不允许执行EXEC,除非执行扩展存储过程。
关键是我存储过程中查找出的数据,别的地方要用,比如
Select * from object where ID in (这里的数据就是存储过程中返回的数据集合的ID字段)
能不能直接
Select * from Object where ID in (Select ID from 存储过程)????
Select * from Object where ID in (Select ID from 存储过程)????
-------------------------------------------------------------------------
Of course U can't.