[分享]在SQL Server中返回分類的子類列表

goodidea

/*創(chuàng)建臨時測試用表*/

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

/*顯示測試結(jié)果*/

select * from fun_getClassCodeListWithChildren('01', default)

select * from fun_getClassCodeListWithChildren('01', 1)

select * from fun_getClassCodeListWithChildren('01', 3)

/*刪除測試表和函數(shù)*/

drop function fun_getClassCodeListWithChildren

drop table tblClassTest