[分享]存储过程数据集返回数据乱码的处理方法**

gaihl

当调用存储过程数据集返回数据,中文变成乱码时, 可能与存储过程中定义类型有关.看下面的例子: 将varchar改成nvarchar时就会正常.

If Object_id('tempdb..#ceshil') Is Not Null
Drop Table #ceshi
create  table #ceshi( personalid varchar(20) ,name varchar(20) ,AttendWorkTime Datetime,leavetype  varchar(20),days float,residual varchar(20),years int)

declare @A1 varchar(20)
declare @A2 varchar(20)
declare @A3 Datetime
declare @A4 varchar(20)
declare @A5 float
declare @A6 varchar(20)
declare @A7 int   


declare   @i   int
set   @i=0
while   @i <=@n
begin

     
declare mycursor cursor for select personal.personalid,personal.name,personal.AttendWorkTime,a1.leavetype,isnull(a1.days,0) days,
convert(varchar(32),(SLeave-isnull(a1.days,0))) residual,a1.years
from (select personalid,psnid,name,AttendWorkTime,case  when (dateadd(yy,10,attendworktime) >@NowDate)
and (dateadd(yy,1,attendworktime) <=@NowDate)  then 5 when(dateadd(yy,20,attendworktime) >@NowDate)
and (dateadd(yy,10,attendworktime) <=@NowDate)  then 10 when dateadd(yy,20,attendworktime) <=@NowDate  then 15
else 0 end SLeave   from personalinfo) personal left join (SELECT personalid,sum(ActualLeaveDays) days ,datepart(year,startingtime) years,leavetype
FROM leave where zt='2' and leavetype ='年休假'and datepart(year,startingtime)=(@StYear+@i)
group by personalid,leavetype,datepart(year,startingtime))a1  on personal.personalID=a1.personalid  
where (isnull (a1.years,0)<>0) and (a1.years=(@StYear+@i)) and (personal.psnid=@NameId)

open mycursor                  
fetch next from mycursor  into @A1,@A2 ,@A3 ,@A4 ,@A5 ,@A6 ,@A7   
while(@@fetch_status=0)     
begin

       INSERT INTO #ceshi VALUES (@A1,@A2,@A3,@A4,@A5,@A6,@A7)   
        fetch next from mycursor into @A1,@A2 ,@A3 ,@A4 ,@A5 ,@A6 ,@A7     

end
close mycursor        
deallocate mycursor


declare mycursor cursor for select  personal.personalid,personal.name,personal.AttendWorkTime,a1.leavetype,isnull(a1.days,0) days,
'/' residual,a1.years
from (select personalid,psnid,name,AttendWorkTime,case  when (dateadd(yy,10,attendworktime) >@NowDate)
and (dateadd(yy,1,attendworktime) <=@NowDate)  then 5 when(dateadd(yy,20,attendworktime) >@NowDate)
and (dateadd(yy,10,attendworktime) <=@NowDate)  then 10 when dateadd(yy,20,attendworktime) <=@NowDate  then 15
else 0 end SLeave   from personalinfo) personal left join (SELECT personalid,sum(ActualLeaveDays) days ,datepart(year,startingtime) years,leavetype
FROM leave where zt='2' and leavetype <>'年休假'and datepart(year,startingtime)=(@StYear+@i)
group by personalid,leavetype,datepart(year,startingtime))a1  on personal.personalID=a1.personalid  
where (isnull (a1.years,0)<>0) and (a1.years=(@StYear+@i)) and (personal.psnid=@NameId)

open mycursor                   --打开游标
fetch next from mycursor  into @A1,@A2 ,@A3 ,@A4 ,@A5 ,@A6 ,@A7    --开始抓第一条数据
while(@@fetch_status=0)     --如果数据集里一直有数据
begin
        INSERT INTO #ceshi VALUES (@A1,@A2,@A3,@A4,@A5,@A6,@A7)    --开始做想做的事（什么更新呀，删除呀）
        fetch next from mycursor into @A1,@A2 ,@A3 ,@A4 ,@A5 ,@A6 ,@A7      --跳到下一条数据
end
close mycursor        --关闭游标
deallocate mycursor  --删除游标



        set @i=@i+1   
end

select *  from #ceshi
Drop Table #ceshi