资质修改查询（经营范围转换为中文）

---删除临时表
IF OBJECT_ID('tempdb..#businesscont') is not null
drop table #businesscont
---检索数据
SELECT t1.Dates,t1.OldBussName,t1.NewBussName,t1.OldAdd,
t1.NewAdd,t1.OldTel,t1.NewTel,t1.OldRegNo,t1.NewRegNo,
t1.OldRegDate,t1.NewRegDate,t1.OldCont,t1.NewCont,
t1.OldMedNo,t1.OldMedDate,t1.OldVDate,t1.NewMedNo,
t1.NewMedDate,t1.NewVDate,t1.OldPhaNo,t1.OldPhaDate,
t1.OldPhaVDate,t1.NewPhaNo,t1.NewPhaDate,t1.NewPhaVDate,
t1.OldHealthNo,t1.OldHeaDate,t1.OldHeaVDate,t1.NewHealthNo,
t1.NewHeaDate,t1.NewHeaVDate,t1.OldGSPNo,t1.OldGSPDate,
t1.OldGSPVDate,t1.NewGSPNo,t1.NewGSPDate,t1.NewGSPVDate,
t1.Summaries,t1.Remark,t2.OrgName,t3.Department,t4.StaffName,t1.BillNo
into #businesscont
FROM BusinessChange t1,OrgDoc t2,DeptDoc t3,StaffDoc t4
WHERE t1.OrgId = t2.OrgId AND t1.DeptId = t3.DeptId 
AND t1.caozy = t4.StaffId 
AND (t1.Dates between :Dates and :Dates__end) 
AND t2.OrgName like :OrgName AND t3.Department like :Department 
AND t1.OrgId='O0V4E28QYGY' 
ORDER BY t1.BillNo asc,t1.Dates asc


---游标处理经营范围
declare @BillNo int 
declare @OldCont varchar(1024)
declare @NewCont varchar(1024)
declare pcurr cursor for select billno,oldcont,newcont from #businesscont
open pcurr 
fetch next from pcurr into @BillNo,@OldCont,@NewCont
while (@@fetch_status = 0)  
begin  
update a set a.oldcont = (select [val]=stuff((
select ','+b.DICTLIST from fn_StrToTable(@OldCont) a
join DICTDOC b on a.goodsid = b.DICTVALUE
where b.FDNAME = 'GCategory'
for xml path('')),1,1,''))
,a.newCont = (select [val]=stuff((
select ','+b.DICTLIST from fn_StrToTable(@NewCont) a
join DICTDOC b on a.goodsid = b.DICTVALUE
where b.FDNAME = 'GCategory'
for xml path('')),1,1,''))
from #businesscont a
where a.billno = @BillNo
 fetch next from pcurr into @BillNo,@OldCont,@NewCont
end  
close pcurr
deallocate pcurr 
----界面显示数据
select * from #businesscont