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,'npic7.edushi.com')<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