这是一个开发实例

来源：互联网发布：mac开机黑屏logo不亮编辑：程序博客网时间：2024/06/06 10:44

需要从俩张表中统计一些信息，对源表也有相应要求，不得一次取多于100000条数据。初稿，有待修改。先保存到这里

create procedure p_license as
begin
declare @addnum numeric(4)
declare @tablestr char(4)
declare @sdate varchar(8)
declare @stime varchar(6)
declare @num mumeric(10)

create table #max_temp(sdate varchar(8),
                         num   numeric(10),
                         timevalue varchar(6),
       sid    numeric(20)
                        )

     set @addnum=5

while (@addnum > 0)
begin
    declare @endid
declare @min_id numeric(20)
declare @max_id numeric(20)
    select @tablestr=right('00'+convert(varchar,datepart(mm,dateadd(day,@addnum,getdate()))),2)
                   +right('00'+convert(varchar,datepart(dd,dateadd(day,@addnum,getdate()))),2)


    begin

       select @endid =select isnull(endid,0)+1
                     from license_log
        where table_name='websubmitlog_t'+@tablestr
    select @min_id=select min(billindex)
                     from 'websubmitlog_t'+@tablestr
        where biilindex>=@endidif (@max_id-@min_id)>1000000
    select @max_id=@min_id+1000001
       insert into max_temp(sdate,num,timevalue,sid)
    select left(convert(varchar,msgid),8) ,count(*) , substring(convert(varchar,msgid),9,14),billindex
         from 'websubmitlog_t'+@tablestr
        where billindex>@min_id
    and billindex<=@max_id
        group by substring(convert(varchar,msgid),9,14),left(convert(varchar,msgid),8),billindex
       if @@error<>0
      begin

     insert into license_log(table_name,startid,endid,processtime,processed)
     values('websubmitlog_t'+@tablestr,@min_id,@max_id,getdate(),0)
     delect from max_temp where sid>@min_id
   end
   else
          insert into license_log(table_name,startid,endid,processtime,processed)
    values('websubmitlog_t'+@tablestr,@min_id,@max_id,getdate(),1)
       insert into db..license(sdate,num,timevalue) select sdate,max(num) ,stime from #max_temp
       truncate table #max_temp
    end
    begin
     select @endid =select isnull(endid,0)+1
                     from license_log
        where table_name='mmscdeliverlog_t'+@tablestr
    select @min_id=select min(billindex)
                     from 'mmscdeliverlog_t'+@tablestr
        where biilindex>=@endidif (@max_id-@min_id)>1000000
    select @max_id=@min_id+1000001
       insert into max_temp(sdate,num,timevalue,sid)
    select left(convert(varchar,msgid),8) ,count(*) , substring(convert(varchar,msgid),9,14),billindex
         from 'mmscdeliverlog_t'+@tablestr
        where billindex>@min_id
    and billindex<=@max_id
        group by substring(convert(varchar,msgid),9,14),left(convert(varchar,msgid),8),billindex
       if @@error<>0
      begin

     insert into license_log(table_name,startid,endid,processtime,processed)
     values('mmscdeliverlog_t'+@tablestr,@min_id,@max_id,getdate(),0)
     delect from max_temp where sid>@min_id
   end
   else
          insert into license_log(table_name,startid,endid,processtime,processed)
    values('mmscdeliverlog_t'+@tablestr,@min_id,@max_id,getdate(),1)
       insert into db..license(sdate,num,timevalue) select sdate,max(num) ,stime from #max_temp
       truncate table #max_temp
    end
    select @addnum=@addnum-1
end
end