|
/*创建临时测试用表*/
create table tblClassTest
(
class_code char (20)
, class_name varchar(50)
, class_code_parent char(20)
)
insert into tblClassTest (class_code,class_name,class_code_parent)
select '01','原材料',null
union all select '0101','高压聚乙烯','01'
union all select '0102','低压聚乙烯','01'
union all select '0103','线性聚乙烯','01'
union all select '0104','色母料','01'
union all select '0106','其他原材料','01'
union all select '0105','回收原料','0106'
union all select '0109','回收高压聚乙烯','0105'
union all select '0107','丁烷气','0106'
union all select '0108','氮气','01'
go
/*
fun_getClassCodeListWithChild
返回分类的子类列表
最后更新:2005/01/02 goodidea (myad@yeah.net)
@intOption = 1 返回其子类
@intOption = 2 返回该类
@intOption = 3 返回该类及其子类
*/
create function fun_getClassCodeListWithChildren (
@ItemClass char(20)
,@intOption smallint = 2
) returns @tblItemClass table (
class_code char (20)
, class_name varchar(50)
, class_code_parent varchar(50)
)
as
begin
insert into @tblItemClass
select class_code, class_name,class_code_parent from tblClassTest
where class_code = @ItemClass
while @@rowcount >0 and (@intOption & 1 = 1)
begin
insert into @tblItemClass
select class_code, class_name,class_code_parent from tblClassTest
where class_code_parent in (select class_code from @tblItemClass )
and class_code not in (select class_code from @tblItemClass )
end
if (@intOption & 2 <> 2) delete @tblItemClass where class_code = @ItemClass
return
end
go
/*显示测试结果*/
select * from fun_getClassCodeListWithChildren('01', default)
select * from fun_getClassCodeListWithChildren('01', 1)
select * from fun_getClassCodeListWithChildren('01', 3)
/*删除测试表和函数*/
drop function fun_getClassCodeListWithChildren
drop table tblClassTest |
|