Audit stored procedures by Time data

Source: Internet
Author: User
Tags rowcount

 Use [Edushi_zixun]GO/** * * * object:storedprocedure [dbo].    [Proc_zx_checkimportdatabyhour] Script date:12/11/2015 13:53:18 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER PROC [dbo].[Proc_zx_checkimportdatabyhour]@CityCode varchar( -) asBegin        --Store a list of posts under the current city    Declare @Temp_IaIdByCity Table(IaIdint    )    Delete  from @Temp_IaIdByCity         --Store A list of answer records under a post    Declare @Temp_IacIdByIaid Table(Iacidint    )    Delete  from @Temp_IacIdByIaid         Declare @importCount int = -    Select @importCount=ISNull(Num,1) fromZxchecknumbyhourWhereH=DatePart(HOUR,GetDate()) andCitycode=@CityCode        IF(@importCount<=0)    Begin        return; End        --set the number of strips obtained    SET ROWCOUNT @importCount    --get @importcount posts in the current city into table variables    Insert  into @Temp_IaIdByCity     Selectia_id frominfoarticleWhereIa_state=0  andIa_isimport=1  andIa_citycode=@CityCode    Order  by NEWID()     SET ROWCOUNT 0            Declare @NowTime datetime,@randomTime datetime,@startTime datetime,@endTime datetime    --an hour ago.    Set @NowTime=DateAdd(HH,-1,GETDATE())            Set @startTime=Convert(datetime,Convert(varchar, Year(@NowTime))+'-'+Convert(varchar,Month(@NowTime))+'-'+Convert(varchar, Day(@NowTime))+' '+Convert(varchar,DatePart(Hour,@NowTime))+': 0:0')    Set @randomTime=@startTime    --Current Time    Set @endTime=GETDATE()    Declare @CountByType int    Set @CountByType=0    Select @CountByType=COUNT(1) from @Temp_IaIdByCity                        --Loop all posts under the current city    Declare @IaId INT    DECLARE @IAContent NVARCHAR(Max)    Set @IaId=0            --Declare @iA int=0, @iB int=0, @iCommentCount int=0         while @IaId>0 --and @importCount > @iA    BEGIN            --Set @[email protected]+1        Select Top 1 @IaId=IaId from @Temp_IaIdByCity            SELECT @IAContent=Ia_content fromDbo.infoarticleWHEREia_id=@IaId        IF(CHARINDEX(@IAContent,'')<0)        BEGIN         CONTINUE; END            --random time within the specified range        ExecProc_zx_getrandomtime@startTime,@endTime,@randomTimeOUTPUT--Update post audit status, and time        UpdateInfoarticleSetIa_state=1, Ia_createdate=@randomTime, Ia_updatedate=@randomTime, Ia_checkdate=@randomTime Whereia_id=@IaId                Delete  from @Temp_IacIdByIaid                --all replies to the current post are staged to a table variable        Insert  into @Temp_IacIdByIaid         Selectiac_id frominfoarticlecommentWhereia_id=@IaId  andIac_isimport=1  andIac_state=0--and [email protected]        Order  byia_id--Set @iCommentCount =0        --Select @iCommentCount =count (1) from @Temp_IacIdByIaid                --Loop all the answers to the current post ia_id        Declare @IacId int        Set @IacId=0        Select Top 1 @IacId=Iacid from @Temp_IacIdByIaid         while @IacId>0 --and @iCommentCount > @iB        Begin            --Set @[email protected]+1                        --the random time within the specified range, from the previous @randomtime to the present            ExecProc_zx_getrandomtime@randomTime,@endTime,@randomTimeOUTPUTUpdateInfoarticlecommentSetIac_state=1, Iac_createdate=@randomTime, Iac_updatedate=@randomTime Whereiac_id=@IacId                            --Next article @iacid            Delete  from @Temp_IacIdByIaid WhereIacid=@IacId            Set @IacId=0            Select Top 1 @IacId=Iacid from @Temp_IacIdByIaid        End                --an incremental update table that is provided to the index using        Insert  intomap_searchrecordstate (msrs_entityid,msrs_name,msrs_state,msrs_createdate)Values(@IaId,'Zxindex',1,getdate())                                --Next article Iaid        Delete  from @Temp_IaIdByCity WhereIaId=@IaId        Set @IaId=0        Select Top 1 @IaId=IaId from @Temp_IaIdByCity            EndEnd

Audit stored procedures by Time data

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.