存储过程,游标,循环,临时表

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 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

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

end

 

GO