按时间数据审核存储过程

USE [edushi_zixun]
GO
/****** Object:  StoredProcedure [dbo].[Proc_ZX_CheckImportDataByHour]    Script Date: 12/11/2015 13:53:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Proc_ZX_CheckImportDataByHour]
@CityCode varchar(50)
AS
Begin
    
    --存放当前城市下的帖子列表
    Declare @Temp_IaIdByCity Table(
        IaId int
    )
    Delete From @Temp_IaIdByCity 
    
    --存放某一帖子下的回答记录列表
    Declare @Temp_IacIdByIaid Table(
        IacId int
    )
    Delete From @Temp_IacIdByIaid 
    
    Declare @importCount int =20
    Select @importCount=ISNull(Num,1) From ZxCheckNumByHour Where H=datepart(HOUR,GetDate()) AND CityCode=@CityCode
    
    IF (@importCount<=0)
    Begin
        return;
    End
    
    --设置取得的条数
    SET ROWCOUNT @importCount
    --取得当前城市下的@importCount条帖子 放入表变量
    Insert into @Temp_IaIdByCity 
    Select IA_ID From InfoArticle
    Where IA_State=0 AND IA_IsImport=1 AND IA_CityCode=@CityCode
    Order By NEWID() 
    SET ROWCOUNT 0
        
    Declare @NowTime datetime,@randomTime datetime,@startTime datetime,@endTime datetime
    --一小时前的时间
    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
    --当前时间
    Set @endTime=GETDATE()

    Declare @CountByType int
    Set @CountByType=0
    Select @CountByType=COUNT(1) From @Temp_IaIdByCity        
            
    --循环当前城市下的所有帖子
    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 @iA=@iA+1
        Select Top 1 @IaId=IaId From @Temp_IaIdByCity    
        SELECT @IAContent=IA_Content FROM dbo.InfoArticle WHERE IA_ID=@IaId
        IF(CHARINDEX(@IAContent,'npic7.edushi.com')<0)
        BEGIN
         CONTINUE;
        END
    
        --指定范围内的随机时间
        Exec Proc_ZX_getRandomTime @startTime,@endTime,@randomTime OUTPUT
        
        --更新帖子审核状态，和时间
        Update InfoArticle Set IA_State=1,IA_CreateDate=@randomTime,IA_UpdateDate=@randomTime,IA_CheckDate=@randomTime Where IA_ID=@IaId
        
        Delete From @Temp_IacIdByIaid
        
        --当前帖子的所有回答暂存到表变量
        Insert into @Temp_IacIdByIaid 
        Select IAC_ID From InfoArticleComment
        Where  IA_ID=@IaId And IAC_IsImport=1 AND IAC_State=0-- And IAC_CityCode=@CityCode
        Order By IA_ID         
        
        --Set @iCommentCount=0
        --Select @iCommentCount=COUNT(1) From @Temp_IacIdByIaid
        
        --循环当前帖子Ia_Id的所有回答
        Declare @IacId int
        Set @IacId=0
        Select Top 1 @IacId=IacId From @Temp_IacIdByIaid
        While @IacId>0 --AND @iCommentCount>@iB
        Begin
            --Set @iB=@iB+1
            
            --指定范围内的随机时间 ，范围为上一个@randomTime到现在之间
            Exec Proc_ZX_getRandomTime @randomTime,@endTime,@randomTime OUTPUT
            
            Update InfoArticleComment Set IAC_State=1,IAC_CreateDate=@randomTime,IAC_UpdateDate=@randomTime Where IAC_ID=@IacId
                
            --下一条@IacId
            Delete From @Temp_IacIdByIaid Where IacId=@IacId
            Set @IacId=0
            Select Top 1 @IacId=IacId From @Temp_IacIdByIaid
        End
        
        --增量更新表，提供给索引使用        
        Insert into Map_SearchRecordState(MSRS_EntityID,MSRS_Name,MSRS_State,MSRS_CreateDate)
        Values(@IaId,'ZXIndex',1,getdate())
            
            
        --下一条IaId
        Delete From @Temp_IaIdByCity Where IaId=@IaId
        Set @IaId=0
        Select Top 1 @IaId=IaId From @Temp_IaIdByCity
        
    End

End

 

转载于:https://www.cnblogs.com/sunxi/p/5097821.html