SQL时间戳使用之任务循环

USE [IwomTask]GO/****** Object:  StoredProcedure [dbo].[GetTaskList]    Script Date: 12/01/2012 02:00:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:arvin-- Create date: 2012-11-21-- Description:获取任务的存储过程，获取未开始和正在进行中但已超过1分钟的任务(暂时调整为一分钟)-- =============================================ALTER PROCEDURE [dbo].[GetTaskList]@SearchEngineIDs varchar(8000)ASBEGINSET NOCOUNT ON;DECLARE @tempTable table(Task_ID int)DECLARE @timestampTable table(timemap bigint)DECLARE @sql varchar(max)SET @sql='SELECT MIN([timemap])FROM [dbo].[Open_Task]WHERE 1=1and IsCrawler=1and (taskState = 1 or (taskState = 2 and taskUpdateDate < dateadd(minute,-1,getdate()))) and SearchEngine_ID in ('+@SearchEngineIDs+')group by SearchEngine_ID'--获得最小的数据集INSERT INTO @timestampTable EXEC (@sql)--获得任务ID的数据集INSERT INTO @tempTableSELECT TK.ID FROM dbo.Open_Task AS TK ,@timestampTable AS TMWHERE TK.timemap=TM.timemap --更新状态位与时间UPDATE  [Open_Task] SET taskState = 2,taskUpdateDate = getdate()WHERE 1=1and [ID]in(SELECT Task_ID FROM @tempTable)--获得数据源SELECT [ProjectID] as Brand_ID    , 1 as GetDays   , [getItems] as GetItems   , [Searchkey] as KeyWord   ,[SearchType] as Task_Postion   ,''as Site_Domain   ,[ID] as Task_ID   ,[SearchEngine_ID] as SearchEngine_ID   ,'' as ExcludeDomain   ,[IncludeKey] as MustInclude   ,[NoContainKey] as NotInclude   ,1 as ResultOrder   ,[taskState] as Task_State   ,[connectionMark] as Task_Mark   ,'1900-01-01 00:00:00.000' as Task_Closetime   , [BeginDate] as Task_Begintime   ,0 as Task_Seg   ,0 as Task_Preview    ,[UserID] as userid   ,[KeyID] as KeyIDFROM [dbo].[Open_Task]WHERE 1=1and [ID]in(SELECT Task_ID FROM @tempTable)END