Create proc up_getenditem(@pID bigint)
as
Beginselect * into T_tmp from T_Master where 1 = 2alter table T_tmp add depth int declare @node bigint
declare @depth int,@SQL varchar(8000)set @node = @pID --指定节点编号
set @depth = 1
set @SQL='insert T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID = '+cast(@node as varchar)
exec(@SQL)while @@ROWCOUNT > 0
begin
set @depth = @depth + 1
set @SQL='insert into T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID in (select itemID from T_tmp WHERE depth='+cast(@depth-1 as varchar)+') '
exec(@SQL)
end
select * from T_tmp where levelName = 6 --我想把这一个表返回drop table T_tmpEnd
as
Beginselect * into T_tmp from T_Master where 1 = 2alter table T_tmp add depth int declare @node bigint
declare @depth int,@SQL varchar(8000)set @node = @pID --指定节点编号
set @depth = 1
set @SQL='insert T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID = '+cast(@node as varchar)
exec(@SQL)while @@ROWCOUNT > 0
begin
set @depth = @depth + 1
set @SQL='insert into T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID in (select itemID from T_tmp WHERE depth='+cast(@depth-1 as varchar)+') '
exec(@SQL)
end
select * from T_tmp where levelName = 6 --我想把这一个表返回drop table T_tmpEnd
as
Beginselect * into T_tmp from T_Master where 1 = 2alter table T_tmp add depth int declare @node bigint
declare @depth int,@SQL varchar(8000)set @node = @pID --指定节点编号
set @depth = 1
set @SQL='insert T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID = '+cast(@node as varchar)
exec(@SQL)while @@ROWCOUNT > 0
begin
set @depth = @depth + 1
set @SQL='insert into T_tmp select *,'+cast(@depth as varchar)+' from T_Master Where ParentID in (select itemID from T_tmp WHERE depth='+cast(@depth-1 as varchar)+') '
exec(@SQL)
end
select * from T_tmp where levelName = 6 --我想把这一个表返回drop table T_tmpEnd
alter table T_tmp add depth int ==>create table T_tmp
(itemID bigint, parentID bigint, label varchar(50), levelname int, depth int)
as
Beginselect *,0 depth into T_tmp from T_Master where 1 = 2declare @node bigint
declare @depth intset @node = @pID --指定节点编号
set @depth = 1insert into T_tmp select *, @depth from T_Master Where ParentID = @nodewhile @@ROWCOUNT > 0
begin
set @depth = @depth + 1 insert into T_tmp
select *, @depth
from T_Master Where ParentID in (select itemID from T_tmp WHERE depth=@depth-1)
end
select * from T_tmp where levelName = 6 --我想把这一个表返回drop table T_tmpEnd