sql servser游标和递归工作中案例

---游标

declare @dname nvarchar(255)
declare cur cursor for  
select left(单位,charindex('|',单位)-1) from(
select distinct(单位) 单位 from view_sys_data_院内科室) temp
open cur
fetch next from cur into @dname
while(@@fetch_status=0)
begin
 insert into Sys_Department_temp(D_Name,d_pid,iscompany)values(@dname,0,1)
 declare @did int
 select @did=IDENT_CURRENT('Sys_Department_temp')--d_pid
 print @dname
 insert into  Sys_Department_temp(D_Name,d_pid,iscompany,tp_id) select 院内科室,@did,0,tp_id from view_sys_data_院内科室 where 单位 like @dname+'%'    
fetch next from cur into @dname
end
close cur
deallocate cur

---递归

 
update Sys_Staff set ext016=(select left(单位,charindex('|',单位)-1) from dbo.view_sys_data_人员管理  where  id=ext010),
ext015=(select 院内科室 from dbo.view_sys_data_人员管理  where  id=ext010)
 
 /*
 select id,s_realname, ext015,ext016,ext010 from sys_staff where id=34339--32669
  select * from Sys_Staff where id=34339
 
 select * from  dbo.view_sys_data_人员管理 where id=32669--第九人民医院|4eccc3be-2537-4757-98a3-805b1379d282辅助生殖科
 select * from Sys_Staff where ext010=32669
 */

with temp as(

select ta.id as pid,ta.d_name as pd_name,tb.id as id,tb.d_name  as d_name from sys_department_temp ta left join sys_department_temp tb on ta.id=tb.d_pid
)
 update sys_staff_temp set s_department1=(select pid from temp where (d_name=ext015 and pd_name=ext016))