一个报表存储过程

create procedure p_query_r27servicesuccrate_day @mmsgids varchar(100),@year char(4),@month varchar(2)
as
begin
  
       declare @mmsgid varchar(10),@mmsgname varchar(50),@databasename varchar(50),@sqlstr varchar(5000)
      

     if object_id ('tempdb..#mmsgreportdb') is not null 
     drop table #mmsgreportdb
     create table #mmsgreportdb
     (
        mmsgid  int, 
        mmsgname   varchar(50),  
        databasename     varchar(50)
      )

     select @sqlstr = 'insert into #mmsgreportdb select mmsgid,mmsgname,databasename from mmsg_report..mmsgreportdb_dim
                        where mmsgid in ('+@mmsgids+') and alive = 1'
      
     execute(@sqlstr)
      
     if object_id('tempdb..#r27') is not null 
     drop table #r27   
       --create temp table

      
       create table #r27
      (
         mmsgid        smallint,
         mmsgname      varchar(50),
         year          smallint,
         month         tinyint,
         day           tinyint,
         mmsc          varchar(50),
         succnum       numeric(36,0),
         totalocount   numeric(36,0),
         netsuccnum    numeric(36,0)
      )
         
          --insert data into #r27org
   declare db_cursor  cursor for
           select convert(varchar(10),mmsgid),mmsgname,databasename from #mmsgreportdb
           open db_cursor
           fetch  db_cursor into @mmsgid,@mmsgname ,@databasename
           while @@sqlstatus = 0
           begin
              select @sqlstr = ' insert into #r27  select '+@mmsgid+' mmsgid,'''+@mmsgname+''' mmsgname,'
                             + ' year,month,date,mmscid+''(''+mmscname+'')'' mmsc,succnum,totalocount,netsuccnum from '   
                             +@databasename+'..r27srvsuccratestat_v_day where year = '+@year+' and month= '+@month+' group by year,month,date'
              exec (@sqlstr)
              select @sqlstr = 'insert into #r27   select '+@mmsgid+' mmsgid,'''+@mmsgname+''' mmsgname,year,month,'
                             + ' date,''Average'',isnull(sum(succnum),0),isnull(sum(totalocount),0), isnull(sum(netsuccnum),0) from '
                             + @databasename+'..r27srvsuccratestat_v_day  where year = '+@year+' and month= '+@month+' group by year,month,date '
              exec (@sqlstr)
           fetch db_cursor into @mmsgid,@mmsgname,@databasename
           end
           close db_cursor

      
             --create r27org table for select
      create table #r27org
      (
         introwstart numeric(20),
         introwend   numeric(20),
         intcolstart numeric(20),
         intcolend   numeric(20),
         strdata     varchar(200)
      )
     declare @ncount int, @lcount int
     select @ncount = 1
     select @lcount = 1
    
      create table #r27org_row
      (
         mmsgid int,
         mmsgname   varchar(20),
         day varchar(2),
         rowid numeric(20) identity
      ) with identity_gap=50

     insert into #r27org_row select distinct t.mmsgid,t.mmsgname,convert(varchar(10),t.day) day
     from #r27 t order by t.mmsgid,day
    
      create table #r27org_col
      (
         mmsc varchar(200),
         rowid numeric(20) identity
      ) with identity_gap=50

     insert into #r27org_col select distinct mmsc from #r27 where mmsc<> 'Average'
     order by mmsc
     insert into #r27org_col values('Average')
      
     insert into #r27org
     select 2,isnull(max(n.rowid),0)+@ncount+1,2*isnull(max(l.rowid),0)+@lcount+1,-1,''
     from #r27org_row n,#r27org_col l

     insert into #r27org values(0,1,0,1,'')/* Adaptive Server has expanded all '*' elements in the following statement */
     insert into #r27org   select r.introwstart, r.introwend, r.intcolstart, r.intcolend, r.strdata                                     from (
     select n.rowid+@ncount introwstart,n.rowid+@ncount introwend,0 intcolstart,0 intcolend,mmsgname strdata
     from #r27org_row n
     union all
     select n.rowid+@ncount,n.rowid+@ncount,1,1,day
     from #r27org_row n
     union all
     select 0,0,2*l.rowid,2*l.rowid+1,mmsc
     from #r27org_col l
     union all
     select 1,1,2*l.rowid,2*l.rowid,'Forwarding Success Rate(%)'
     from #r27org_col l
     union all
     select 1,1,2*l.rowid+1,2*l.rowid+1,'Network Forwarding Success Rate(%)'
     from #r27org_col l
     )r

     /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #r27org
     select r.introwstart, r.introwend, r.intcolstart, r.intcolend, r.strdata                                  from (
     select t2.introwstart introwstart,t2.introwend introwend,t3.intcolstart intcolstart,t3.intcolstart intcolend,
           (case when  totalocount is null then '0.00'
                 when totalocount=0  then '0.00'
                 when 100.00*convert(float,succnum)/totalocount>100 then '100.00'
                 when 100.00*convert(float,succnum)/totalocount<0 then '0.00'
                 else convert(varchar(6),convert(numeric(5,2),round((100.00*convert(float,succnum)/totalocount),2))) end) strdata
     from #r27 t1
     inner join #r27org t2 on convert(varchar(10),t1.day) = t2.strdata
     inner  join #r27org t3 on t1.mmsc = t3.strdata
     inner join #r27org t4 on t1.mmsgname = t4.strdata
     where t2.intcolstart = 1 and t2.introwstart > 1 and t3.introwstart = 0 and t3.introwend = 0 and t3.intcolstart >1 and t4.intcolstart =0
     and t2.introwstart = t4.introwstart
     union all
     select t5.introwstart,t5.introwend,t6.intcolend,t6.intcolend,
            (case  when  totalocount is null then '0.00' 
                   when totalocount=0  then '0.00'
                   when (100.00*convert(float,netsuccnum)/totalocount)>100 then '100.00'
                   when (100.00*convert(float,netsuccnum)/totalocount)<0   then '0.00'
                   else convert(varchar(6),convert(numeric(5,2),round((100.00*convert(float,netsuccnum)/totalocount),2))) end)
     from #r27 t8
     inner  join #r27org t5 on convert(varchar(10),t8.day) = t5.strdata
     inner  join #r27org t6 on t8.mmsc = t6.strdata
     inner  join #r27org t7 on t8.mmsgname = t7.strdata
     where t5.intcolstart = 1 and t5.introwstart > 1 and t6.introwstart = 0 and t6.introwend = 0 and t6.intcolstart >1 and t7.intcolstart =0
     and t5.introwstart = t7.introwstart
     ) r
 --    update #r27org set strdata = '0'+strdata where string(strdata,1,1)='.'
    
    
     --select from r27org table
     /* Adaptive Server has expanded all '*' elements in the following statement */ select #r27org.introwstart, #r27org.introwend, #r27org.intcolstart, #r27org.intcolend, #r27org.strdata from  #r27org

end