急！存储过程循环不走的问题
0
悬赏园豆：10
[已关闭问题]
关闭于 2010-08-30 17:39
declare @UpMasterID varchar(50)
declare @ItemMasterID varchar(50)
set @ItemMasterID='a4a3b6b4-6a27-4174-ae2e-f38d719336da'
set @UpMasterID = @ItemMasterID
declare @hasUp int
set @hasUp = 1
while (@hasUp != 0)
begin
set @ItemMasterID = (select b.ItemMasterID
from CAPP_BaseInfo b where b.ID in (select c.BaseID from CAPP_BomLine c
where c.ItemMasterID=@UpMasterID))
set @hasUp = @@rowcount
if @hasUp != 0
begin
set @UpMasterID = @ItemMasterID
end
end
select @UpMasterID
比如
1
2
3
4
我从4向上找，就是要找到1，现在好像是死循环，查老长时间，请教高手，感激不尽！
所有回答(2)
0
数据库版本是？
好像这跟数据库版本没有关系吧，我只是要从一个表中的递归数据中找到最上层的数据
2000 不能用递归CTE，2005能用递归CTE解决，这就是版本的关系。 SQL Server 2005 or Higher with data as ( select 1 as ItemMasterID , 1 as UpMasterID union all select 2 as ItemMasterID , 1 as UpMasterID union all select 3 as ItemMasterID , 2 as UpMasterID union all select 4 as ItemMasterID , 3 as UpMasterID union all select 5 as ItemMasterID , 4 as UpMasterID union all select 11 as ItemMasterID , null as UpMasterID union all select 12 as ItemMasterID , 11 as UpMasterID union all select 13 as ItemMasterID , 12 as UpMasterID union all select 14 as ItemMasterID , 13 as UpMasterID union all select 15 as ItemMasterID , 14 as UpMasterID ) ,item_root as ( select 0 as level , ItemMasterID ,UpMasterID from data where ItemMasterID=4 --^ 修改开始的节点编号 union all select level+1 as level , d.ItemMasterID ,d.UpMasterID from data d inner join item_root i on d.ItemMasterID = i.UpMasterID and d.ItemMasterID <> i.ItemMasterID ) select ItemMasterID as root from item_root where level=(select MAX(level) from item_root) 如果你是2000的库，检查一下根节点的 upmasterid 是不是和 itemmasterid 的值一样。
TMD 格式都乱了
0
啥意思呢.
看代码有点像寻找根节点。
