Sybase 代替start with、connect by的store procedure

if exists (select 1 from sysobjects where type='P' and name ='tree_p_lujn')
DROP PROC tree_p_lujn
go
create proc tree_p_lujn(
@is_root_start_id varchar(100),
@is_table_name varchar(100),
@is_colume_id_name varchar(100),
@is_colume_parent_id_name varchar(100)
)
as
declare
@vn_pointer int,
@vn_lsmore int,
@vs_sql_temp varchar(2000)
begin
set @vs_sql_temp ="
create table #tmp(pid int not null,id int not null ,curr int not null,le varchar(255) not null)

 

--insert into #tmp values('-1','"+@is_root_start_id+"',-1,'.')

insert into #tmp
select 0,convert(int,t."+@is_colume_id_name+"),0,'.'+convert(varchar,t."+@is_colume_id_name+")
from "+@is_table_name+" t
where t."+@is_colume_parent_id_name+" ='"+@is_root_start_id+"'
set @vn_lsmore=@@rowcount,@vn_pointer=0

while(@vn_lsmore>0)
begin
insert into #tmp
select convert(int,t."+@is_colume_id_name+"),convert(int,t."+@is_colume_id_name+"),
@vn_pointer+1,u.le+'.'+convert(varchar,t."+@is_colume_id_name+")
from "+@is_table_name+" t,#tmp u
where convert(int,t."+@is_colume_parent_id_name+")=u.id and u.curr=@vn_pointer

set @vn_pointer=@vn_pointer+1,@vn_lsmore=@@rowcount
end

select t.* from "+@is_table_name+" t,#tmp u
where u.id=convert(int,t."+@is_colume_id_name+") order by le"
print @vs_sql_temp
exec (@vs_sql_temp)
return
end

----------测试-------------
exec tree_p_lujn '0','TBL_TEST','ID','PID'