SQLServer實現樹型結構資料查詢的預存程序,類似oracle的connect by

CREATE PROCEDURE [dbo].[sp_get_tree_relation]
(@table_name nvarchar(50), @id nvarchar(50), @name nvarchar(50), @parent_id nvarchar(50), @startId nvarchar(20))
AS
    declare @v_id int
    declare @v_level int
    declare @sql nvarchar(500)
begin
    create table #temp (id nvarchar(20),name nvarchar(50),parent_id nvarchar(20))
    create table #t1 (id nvarchar(20),name nvarchar(50),parent_id nvarchar(20),level int)
    -- 將需要查詢關係的表的資料先匯入到暫存資料表
    set @sql='insert into #temp select '+@id+','+@name+','+@parent_id +' from '+@table_name
    exec sp_executesql @sql

    set @v_level=1
    set @v_id=@startId
    insert #t1 select a.id,a.name,a.parent_id,@v_level from #temp a where a.id=@v_id
    while @@rowcount>0
    begin
        set @v_level=@v_level+1
        insert #t1 select a.id,a.name,a.parent_id,@v_level
        from #temp a where a.parent_id in
        (select id from #t1 where level=@v_level-1)
    end

    select a.level,a.id,a.name,a.parent_id,b.name parent_name
    from  #t1 a  left outer join #temp b
    on a.parent_id = b.id order by a.level asc
end

--執行
如果表organize為層級關係的表,則執行預存程序得到組織關係的樹型結果
sp_get_tree_relation 'orgnaize','org_id','org_name','parent_org',1