使用循环查找父级内容

 

 

alter PROCEDURE [dbo].[UP3_TDept_TDtID_BydeptID]

       @deptID int

AS

BEGIN

       DECLARE @id INT

       SET @id=@deptID

 

create table #temp

(

    [TDtID] [int] ,

       [TDtPID] [int] NULL,

       [TDtName] [varchar](50) ,

       [TDtSort] [int],

       [TDtFlag] [int] ,

       [TDtComNo] [varchar](100) ,

       [TDtLevel] [int]

)

 

WHILE(SELECT COUNT(*) FROM dbo.TDept WHERE  TDtID=@id)>0

              BEGIN

 

                     insert into #temp

                     SELECT *  FROM dbo.TDept WHERE  TDtID=@id

                     SET @id=(SELECT TDtPID FROM dbo.TDept WHERE  TDtID=@id);

 

              END

                     select * from #temp

END