存储过程计算报表

create procedure usp_t_scdd_otherMine 
-- @iPageSize int,@iCurPage int,
 @iYear int,@sDwID varchar(255)
--with encryption
as
 begin
  --创建临时表
  create table #t1
   (
   FNo int identity not null,      /*序号*/ 
   FDwID int null,        /*单位ID*/
   FDwCode varchar(255) null,      /*单位编码*/
   FDwName varchar(255) null,      /*单位名称*/
   FNum numeric(14,2),       /*总量*/
   FType varchar(255) null,      /*类型：计划和实际*/
   FYear int null,        /*年份*/
   F1 numeric(14,2),
   F2 numeric(14,2),   
   F3 numeric(14,2),   
   F4 numeric(14,2),   
   F5 numeric(14,2),   
   F6 numeric(14,2),   
   F7 numeric(14,2),   
   F8 numeric(14,2),   
   F9 numeric(14,2),   
   F10 numeric(14,2),   
   F11 numeric(14,2),   
   F12 numeric(14,2),  
   )

  --插入单位ID，单位名称和领导列数据
  if(ltrim(rtrim(@sDwID))='') set @sDwID='%'  

  /*插入计划初始数据*/
  insert into #t1 (FDwID,FDwCode,FDwName,FType,FYear)
   select distinct t2.FID,t2.FCode FDwCode,t2.FName FDwName,'计划' FTypeName,t1.FYear
   from t_scdd_otherMine t1
    left join t_sys_dw t2 on t1.FDwID=t2.FID
   where t1.FYear=@iYear and t1.FDwID like @sDwID
   order by t2.FCode

  /*插入实际初始数据*/
  insert into #t1 (FDwID,FDwCode,FDwName,FType,FYear)
   select distinct t2.FID,t2.FCode FDwCode,t2.FName FDwName,'实际' FTypeName,t1.FYear
   from t_scdd_otherMine t1
    left join t_sys_dw t2 on t1.FDwID=t2.FID
   where t1.FYear=@iYear and t1.FDwID like @sDwID
   order by t2.FCode

  --插入其他列数据
  declare @iFDwID int

  DECLARE Dw_cursor CURSOR FOR
   select FDwID from #t1 order by FDwCode

  OPEN Dw_cursor

  FETCH NEXT FROM Dw_cursor
   INTO @iFDwID

  WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE content_cursor CURSOR FOR
    select t1.FMonth,t1.FPlan,t1.FAct from t_scdd_otherMine t1
     left join t_sys_dw t2 on t1.FDwID=t2.FID
    where t1.FYear=@iYear and t1.FDwID=@iFDwID
    order by FDwID
--   ,@iSum int
   declare @iFMonth int,@sFPlan varchar(255),@sFAct varchar(255)

   OPEN content_cursor
   FETCH NEXT FROM content_cursor INTO @iFMonth,@sFPlan,@sFAct

--   set @iSum=0
   WHILE @@FETCH_STATUS = 0
   BEGIN
     /*更新计划*/
     if (@iFMonth=1)  Update #t1 Set F1=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=2)  Update #t1 Set F2=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=3)  Update #t1 Set F3=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=4)  Update #t1 Set F4=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=5)  Update #t1 Set F5=@sFPlan where FDwID=@iFDwID and FType='计划'

     if (@iFMonth=6)  Update #t1 Set F6=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=7)  Update #t1 Set F7=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=8)  Update #t1 Set F8=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=9)  Update #t1 Set F9=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=10)  Update #t1 Set F10=@sFPlan where FDwID=@iFDwID and FType='计划'

     if (@iFMonth=11)  Update #t1 Set F11=@sFPlan where FDwID=@iFDwID and FType='计划'
     if (@iFMonth=12)  Update #t1 Set F12=@sFPlan where FDwID=@iFDwID and FType='计划'

     /*更新实际*/
     if (@iFMonth=1)  Update #t1 Set F1=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=2)  Update #t1 Set F2=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=3)  Update #t1 Set F3=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=4)  Update #t1 Set F4=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=5)  Update #t1 Set F5=@sFAct where FDwID=@iFDwID and FType='实际'

     if (@iFMonth=6)  Update #t1 Set F6=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=7)  Update #t1 Set F7=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=8)  Update #t1 Set F8=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=9)  Update #t1 Set F9=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=10)  Update #t1 Set F10=@sFAct where FDwID=@iFDwID and FType='实际'

     if (@iFMonth=11)  Update #t1 Set F11=@sFAct where FDwID=@iFDwID and FType='实际'
     if (@iFMonth=12)  Update #t1 Set F12=@sFAct where FDwID=@iFDwID and FType='实际'

--     set @iSum=@iSum+1
    
    FETCH NEXT FROM content_cursor INTO @iFMonth,@sFPlan,@sFAct
   END

   CLOSE content_cursor
   DEALLOCATE content_cursor
--   Update #t1 Set FSum=@iSum where FNo=@iFNo
    
   -- Get the next author.
   FETCH NEXT FROM Dw_cursor
    INTO @iFDwID

  END

  CLOSE Dw_cursor
  DEALLOCATE Dw_cursor

  --分页显示数据
  declare @sSql nvarchar(1000)
  select @sSql=
   '
    SELECT * FROM #t1
    ORDER BY FDwCode,FType
   '
  execute sp_executesql @sSql

  --返回总行数
--  select count(*) from #t1
 end
--GO