有以下表
id(节点) Pr_id(父节点) Name
1 xx1
2 1 xx2
3 2 xx3
...
保存了一个树形结构
数据库是SQLServer7.0
如何实现用存储过程获得任何节点下的所有零件
注意:SQLServer7.0不要自定义函数
id(节点) Pr_id(父节点) Name
1 xx1
2 1 xx2
3 2 xx3
...
保存了一个树形结构
数据库是SQLServer7.0
如何实现用存储过程获得任何节点下的所有零件
注意:SQLServer7.0不要自定义函数
*/CREATE PROCEDURE pBOMViewList(@intProdID int)
AS
Declare @LevCount Integer
set nocount on
Select @LevCount=1
select fPartID into #dBOMA from tBOM where fParentID=@intProdIDselect fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
into #dBOM from tBOM,tProduct
where fParentID=@intProdID and tProduct.fID=tBOM.fPartID
Insert #dBOM(fParentID,fPartID,name,fQty)-----将原产品加入
select fParentID=0,fPartID=@intProdID,name=rtrim(ltrim(IsNull(fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),1
from tProduct
where fID=@intProdID
select fPartID=@intProdID into #dBOMBwhile (select count(*) from #dBOMA)>0
begin
if (@LevCount>20)
begin
goto Out
end
Insert #dBOM(fParentID,fPartID,name,fQty)
select fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
from tBOM,tProduct
where fParentID in (select fPartID from #dBOMA) and tBOM.fPartID=tProduct.fID
delete from #dBOMB
insert #dBOMB select fPartID from #dBOMA delete from #dBOMA
insert #dBOMA
select fPartID from tBOM
where fParentID in (select fPartID from #dBOMB)
Delete from #dBOMB
Select @LevCount=@LevCount+1
end
out:select distinct * from #dBOM
set nocount off
GO