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
    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'