T-SQL 存储过程

jljlpch

prk/彭仁夔      08-09-02        转载注明出处。
在开发中，我们经常要用到这样的统计
 
根据用户输入的日期，把每个月的备份表的数据和正在使用的表中的综合起来统计。假如2008-05-11到2008-08-01，那么就包含5，6，7，8四张备份表还有正在使用的表，加起5张表。这是要统计的所有的数据。
 
对于统计又有要求，分情况来统计各种情况的总计，但是有的特殊情况要统计其明细。
如下图：
 T-sql日期转换不兼容。还有对运行的sql的长度也有一定的限制。
 
 
prk/彭仁夔      08-09-02        转载注明出处。
 
 
ALTER PROCEDURE [dbo].[UP_TSMReceivedDailyReport]@StartTime varchar(100),@EndTime    varchar(100),@OrgAddrs  varchar(1000)='',@MsgType_IDs  varchar(1000)='' AS      declare @where  varchar(1000)     declare @startMonth  int      declare @endMonth int      declare @tempMonth int     declare @generalSql varchar(1000)     declare @tempSql varchar(1000)     declare @tempSql1 varchar(3000)     declare @tempTotal varchar(3000)     declare @tempUndeal varchar(3000)     declare @tempdealing varchar(3000)     declare @tempdealed varchar(3000)     declare @temp1 varchar(3000)     declare @temp2 varchar(3000)     declare @temp3 varchar(3000)     declare @temp4 varchar(3000)     declare @mm int     declare @mm1 int  /*    declare @StartTime varchar(100)    declare @EndTime    varchar(100)    declare @OrgAddrs  varchar(1000)    declare @MsgType_IDs  varchar(1000)    set @StartTime ='2008-05-05'   set  @EndTime  ='2008-07-07'   set  @OrgAddrs =''   set  @MsgType_IDs =''*/    set  @startMonth=Month(@StartTime)    set  @endMonth =Month(@EndTime)    set  @tempMonth=@startMonth    set  @tempSql=' '    set  @tempSql1=' '    set  @temp1=' '    set  @temp2=' '    set  @temp3=' '    set  @temp4=' '    set  @where=' where recvTime>='''+CAST(@StartTime as varchar(20))+''' and recvTime<='''+CAST(@EndTime as varchar(20))+'''  '    if @OrgAddrs!=''     begin      set @where=@where+' '+ ' and OrgAddr in '+@OrgAddrs+' '      end        if @MsgType_IDs!=''       begin       set @where=@where+' '+ ' and MsgType_ID in '+@MsgType_IDs+' '       end     print @where     set @generalSql=' select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime,       SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content            from T_SMReceived '+@where+' and DealSign<>4  and (DealSign<>8 and iswait<>0) and (DealSign<>3 and IsWait<>0)'       print @generalSqlcreate table #temp  (RecvSM_ID int,                       User_ID int,                       MsgType_ID int,                      OrgAddr varchar(128),                      DestAddr varchar(128),                      RecvTime datetime,                      SM_Content varchar(6000),                      DealSign tinyint,                      DealTime datetime,                     IsWait bit,                      ReMsgType_ID int ,                     SendSM_Content varchar(6000) )       insert into #temp exec (@generalSql)  while @tempMonth<=@endMonth       begin         set @tempSql='select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime,       SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content   from T_SMReceived_'+Cast(@tempMonth as varchar(2))+'  '+@where+' '     insert into #temp  exec (@tempSql)                   set @tempMonth= @tempMonth+1     end              select @mm= count(*) from #temp    print 'the temp table have:'+cast(@mm as varchar(100))     set @temp1='select  msgtype_id  ,convert(varchar(10),recvtime,120) recvtime , total=count(*)     from #temp  '     set @temp2=' group by  convert(varchar(10),recvtime,120), msgtype_id  '     set @tempTotal = @temp1+''+@temp2     print @tempTotal     set @tempUndeal = @temp1+' where (iswait=1 and DealSign=0) or DealSign=2  or (iswait=1 and DealSign=8) '+@temp2     print @tempUndeal     set @tempdealing =@temp1+' where (iswait=1 and DealSign=1) or (iswait=1 and DealSign=9) '+@temp2     print @tempdealing     set @tempdealed =@temp1+' where (iswait=0 and DealSign=3) or (iswait=0 and DealSign=4) '+@temp2     print @tempdealed     set @temp3='select a.MsgType_ID , a.RecvTime,a.total,     (select undeal= case  when b.total IS NULL  then 0 else b.total end) undeal ,     (select dealing= case  when c.total IS NULL  then 0 else c.total end) dealing,     (select dealed= case  when d.total IS NULL  then 0 else d.total end) dealed,      undealDetail=null,dealingDetail=null,dealedDetail=null from ('+@tempTotal+') a left join ('        +@tempUndeal+') b on a.msgtype_id=b.msgtype_id and a.recvtime=b.recvtime left join ('+        @tempdealing+' ) c on a.msgtype_id=c.msgtype_id and a.recvtime=c.recvtime left join ( '        +@tempdealed+') d on a.msgtype_id=d.msgtype_id and a.recvtime=d.recvtime'       print '#temp1 SQL: '+ @temp3create table #temp1  (                     MsgType_ID int,                                           RecvTime varchar(10),                      total int,                     undeal int,                                           dealing int,                                          dealed int,                     undealDetail varchar(1000),                     dealingDetail varchar(1000),                     dealedDetail varchar(1000),                                         )  insert into #temp1   exec(@temp3)      select @mm1= count(*) from #temp1print 'the #templ have :'+cast(@mm1 as varchar(100))declare finalReport cursor   for select MsgType_ID,convert(varchar(10),recvtime,120) RecvTime ,dealed from #temp1   open finalReport     declare @MsgType_IDf int     declare @RecvTimef varchar(10)     declare @dealedf int      print  'start tatal one record';     fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf      while(@@fetch_status=0)         begin             declare my_cursor cursor                             for  select a.msgtype_id ,a.recvtime,a.size,a.reMsgType_ID,b.ReMsgTypeName from                     (select msgtype_id,count(*) size, convert(varchar(10),recvtime,120) recvtime,reMsgType_ID from #temp                        where convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf and (iswait=0 and (DealSign=3 or DealSign=4))   group by  convert(varchar(10),recvtime,120), msgtype_id,reMsgType_ID) a left join T_ReMsgType b                      on a.reMsgType_ID=b.reMsgType_ID order by a.msgtype_id                open my_cursor                 declare @msgtype_id int                 declare @recvtime varchar(10)                 declare @reMsgType_ID int                 declare @ReMsgTypeName varchar(500)                 declare @dealedDetail varchar(500)                 declare @size int                 declare @flag int                 set @flag=0                 set @dealedDetail='总数:'+Cast(@dealedf as varchar(5))+' (其中 '                  print  'start deal one record';               fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName                 while(@@fetch_status=0)                       begin                         set @dealedDetail= @dealedDetail+@ReMsgTypeName+': '+                                          Cast((select undeal= case  when @size IS NULL  then 0  else @size end) as varchar(5))+','                          set @flag=1                                             fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName                       end                   if @flag=0                       begin                       set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail)-3)                       end                    else                        begin                         set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail))+')'                       end                close my_cursor                 DEALLOCATE my_cursor          update #temp1 set dealedDetail= @dealedDetail  where convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf             print  'have update one record';     fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf          end    close finalReport    DEALLOCATE finalReport select c.*,d.MsgTypeName from #temp1 c left join T_MsgType d on c.msgtype_id=D.msgtype_id  order by c.RecvTime,c.msgtype_id