HR_ATTabn_qry2一个复杂的存贮过程

CREATE  PROCEDURE  HR_ATTabn_qry2
@D_date nvarchar(10)=null,
@deptno nvarchar(1000)=null
 as
begin

 declare @sql nvarchar(1000)
 
 --创建临时表
create TABLE #MyTempTable (deptno nvarchar(10),
  deptname nvarchar(10),
  empno nvarchar(10),
  cname nvarchar(10),
  abn_reason nvarchar(200),
  abn_in nvarchar(10),
  abn_out nvarchar(10),
  patro_type nvarchar(10)
  )
create TABLE #MyTempTable2 (deptno nvarchar(10),
  deptname nvarchar(10),
  empno nvarchar(10),
  cname nvarchar(10),
  abn_reason nvarchar(200),
  abn_in nvarchar(10),
  abn_out nvarchar(10),
  patro_type nvarchar(10),
  abn_reason_value nvarchar(2)
  )

set @sql=' insert into #MyTempTable select
 B.deptno,
                  dbo.GetDeptmentName(B.deptno) as DeptName,
                A.empno,
 dbo.getHRCName(A.empno)as CName,
 Abn_reason,
 abn_in,abn_out,patro_type
 
              from HR_ATTabn A,HRempM B where abn_flag = ''N'' and A. empno=B.empno and B.deptno  in('+@deptno+') and D_date='''+@d_date+'''  order by B.deptno, A.empno,patro_type'

 print(@sql)
 exec(@sql)

-- select * from #MyTempTable

 declare @itemCount int

 --select distinct empno from #MyTempTable
 set @itemCount = (select   @@rowcount)

 --select @itemCount as cc

/*游标*/
 declare @empno nvarchar(10)
 DECLARE Emp_Cursor CURSOR FOR
  select distinct empno from #MyTempTable
 OPEN Emp_Cursor
 FETCH NEXT FROM Emp_Cursor into @empno
 WHILE @@FETCH_STATUS = 0
 BEGIN  
  declare  @ptype int
  set @ptype = 1001

  --类别循环
  while(@ptype <= 1004)
  begin
   /**2-1*/
   declare @abn_reason nvarchar(10)
   declare @abn_reason2 nvarchar(200)
   set @abn_reason = ''
   set @abn_reason2 = ''
   --select abn_reason from #MyTempTable where empno = @empno and patro_type = @ptype
   
   --游标开始
   DECLARE tmp_cursor Cursor for
    select abn_reason from #MyTempTable where empno = @empno and patro_type = @ptype
   OPEN tmp_cursor
   FETCH NEXT FROM tmp_cursor into @abn_reason
   WHILE @@FETCH_STATUS = 0
   BEGIN   
    set @abn_reason = dbo.Get_AbnReason_ByCode(@abn_reason)
    if @abn_reason2 != ''
     set @abn_reason2 = @abn_reason2 + ', ' + @abn_reason
    else
     set @abn_reason2 = @abn_reason
    
   FETCH NEXT FROM tmp_cursor into @abn_reason
   END
   CLOSE tmp_cursor
   DEALLOCATE tmp_cursor
   --游标结束

   /**保存记录*/
   if @abn_reason2 is not null and @abn_reason2 != ''
    begin
    set @abn_reason2 = dbo.Get_PatroType_ByCode(@ptype) + @abn_reason2
    insert into #MyTempTable2(
     deptno, deptname ,empno ,cname ,abn_reason, abn_in ,abn_out ,patro_type, abn_reason_value
    ) select top 1 deptno, deptname ,empno ,cname ,@abn_reason2 as a, abn_in ,abn_out ,patro_type, abn_reason from #MyTempTable where empno = @empno and patro_type = @ptype
    end   
   set @ptype = @ptype + 1
  end--while end

  FETCH NEXT FROM Emp_Cursor into @empno
 END
 CLOSE Emp_Cursor
 DEALLOCATE Emp_Cursor
  --游标结束
 --想要的数据
 select * from #MyTempTable2 order by deptno, empno

 --删除临时表
 drop table #MyTempTable, #MyTempTable2

end

GO