使用游标实现用‘--’连接两列数据

5、实现父级部门和子级部门相连

-- select dbo.[Select_TDtID_SelectTpName](123)

 

ALTER function [dbo].[Select_TDtID_SelectTpName](@TDtID varchar(60))

returns   varchar(100)

as

begin

declare @id int;

declare @carNum varchar(100), @finalname varchar(200), @name varchar(50)

    DECLARE @AA Table(TDtID int null,[TDtName] varchar(50));

set @finalname='';

 

with  

    RTU1 as( 

        select TDtID ,TDtPID,[TDtName] from TDept 

    ), 

    RTU2 as( 

        select * from RTU1 where TDtID=@TDtID

        union all 

        select RTU1.* from RTU2 inner join RTU1  

        on RTU2.TDtPID=RTU1.TDtID

       ) 

    insert into @aa  

    select TDtID,[TDtName]  from RTU2;

DECLARE Employee_Cursor CURSOR  local FOR

(  

select *  from @aa   

     )

 

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor into @id,@name

WHILE @@FETCH_STATUS = 0

 BEGIN

     set @finalname = @finalname +'-'+ @name

 

     FETCH NEXT FROM Employee_Cursor into @id,@name

 END

CLOSE Employee_Cursor;

return @finalname

 

end