求一存储过程,狂谢! 有点难哦.100分求助
表结构:
ID TypeName SupTypeID
----------- ---------------- -----------
1 Agriculture 0
2 Apparel & Fashion 0
3 Automobile 0
4 Business Services 0
5 Agriculture & By-product Agents 1
6 Agriculture Product Stocks 1
7 Agriculture Products Processing 1
8 Other 6
9 Animal Extract 6
10 Animal Fodders 7

----------------------------

SupTypeID代表 上级目录 0表示一级目录
SupTypeID=1表示为ID=1的子目录，依此类推，ID=8 是ID=6 的子目录

我现在想，读取ID=1时把他所有的产品读出来

PID ProductName TypeID State
----------- -------------------------------------------------
1 dfdsf 8 1
2 dfdsfdsf 9 0
3 dfdfd32 10 1
4 dsfdsf 8 1
5 dfdsfds 6 2

TypeID 为目录的ID

Catalog是个多目录在一个表里体现出来的

这样的存储过程怎么写呢，

[解决办法]
一函就可以
假表名“表”和“产品表 ”

Create Function F_GetChildren(@ID Int)
Returns @Tree Table (ID Int, SupTypeID Int)
As
Begin
Insert @Tree Select ID, SupTypeID From 表 Where ID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.SupTypeID From 表 A Inner Join @Tree B On A.SupTypeID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
GO
Select * From 产品表 A
Inner Join dbo.F_GetChildren(1) B
On A.PID = B.ID
GO
[解决办法]
create procedure pro (@id varchar 1)
as
begin
select * from table where exists (select 1 from table where suptypeid=@id )
end

[解决办法]
varchar(10)
[解决办法]
drop table productlist
if not exists(select 1 from sysobjects where name = 'productlist ' and type = 'U ')
begin
create table productlist
(
id int not null,
typename varchar(64) not null,
suptypeidintnot null
)

create unique index idx_productlist on productlist(id)
insert into productlist
select 1 , 'Agriculture ', 0
union select 2 , 'Apparel & Fashion ', 0
union select 3 , 'Automobile ', 0
union select 4 , 'Business Services ', 0

union select 5 , 'Agriculture & By-product Agents ', 1
union select 6 , 'Agriculture Product Stocks ', 1
union select 7 , 'Agriculture Products Processing ', 1
union select 8 , 'Other ', 6
union select 9 , 'Animal Extract ', 6
union select 10, 'Animal Fodders ', 7

end
go
if exists(select 1 from sysobjects where name = 'sp_get_subproductlist ' and type = 'P ')
drop proc sp_get_subproductlist
go
create procedure sp_get_subproductlist
@id int
as
begin
set nocount on
select * into #productlist from productlist
where id = @id

while exists(select 1 from productlist
where suptypeid in (select id from #productlist) and id not in (select id from #productlist))
begin

insert into #productlist
select *
from productlist
where suptypeid in (select id from #productlist)
and id not in (select id from #productlist)
end

select * from #productlist

end
go

exec sp_get_subproductlist 1