ALTER PROCEDURE [dbo].[testpro]

as

declare @yeardatestr varchar(20) --日期拼接

declare @meterno varchar(20) --仪表编号

declare @collectindataname varchar(30) --数据采集表

declare @collectindataname_back varchar(30) --数据采集备份表

declare @monsdt varchar(20)

declare @monedt varchar(20)

begin

    set @yeardatestr = datename(YY,getdate()) + datename(MM,getdate()) 

    set @monsdt=Convert(VarChar(4),DatePart(year,GETDATE()))+'-'+Convert(VarChar(2),DatePart(MONTH,DateAdd(MONTH,-2,GETDATE())))+'-01'

    set @monedt=CONVERT(VARCHAR(10),DateAdd(DAY,-1,DateAdd(MONTH,2,Convert(datetime,@monsdt,121))),20)

    declare meters_cur cursor local  fast_forward --定义游标

    for

    select distinct MeterNo from MeterInfo

    open meters_cur --打开游标

    fetch next from meters_cur into @meterno--从游标中取出数据

    while @@fetch_status =0 --取出数据成功

    begin

        dbcc SHRINKFILE(testdb_log,0) --收缩日志

        fetch next from meters_cur into @meterno--下一条

    end

    close meters_cur--关闭游标

    deallocate meters_cur--释放游标

end

    nextValue:--循环起点关键字

    select @num=ynum from pp_yunnum where prjsht=@prjsht

    IF(@flg=1)

        set @yunsht='K' + @prjsht + '-' + Convert(varchar(10),@num+1)

    ELSE

        set @yunsht=REPLACE(@prjsht,'_','') + '-' + dbo.Lpad(@num+1,2,'')

    if exists (select 1 from pp_yun where pp_yun.sht=@yunsht)

    begin

        update pp_yunnum set ynum=ynum+1 where prjsht=@prjsht

        goto nextValue;--重新从nextValue出执行

    end