SQL时间戳使用之任务循环

USE [IwomTask]
GO
/****** Object:  StoredProcedure [dbo].[GetTaskList]    Script Date: 12/01/2012 02:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		arvin
-- Create date: 2012-11-21
-- Description:	获取任务的存储过程，获取未开始和正在进行中但已超过1分钟的任务(暂时调整为一分钟)
-- =============================================
ALTER PROCEDURE [dbo].[GetTaskList]
	@SearchEngineIDs varchar(8000)
AS
BEGIN
	SET 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=1
	and IsCrawler=1
	and (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 @tempTable
	SELECT TK.ID FROM dbo.Open_Task AS TK ,@timestampTable AS TM
	WHERE TK.timemap=TM.timemap 
	--更新状态位与时间
	UPDATE  [Open_Task]
	 SET taskState = 2
		,taskUpdateDate = getdate()
	WHERE 1=1
	and [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 KeyID
	FROM [dbo].[Open_Task]
	WHERE 1=1
	and [ID]in(SELECT Task_ID FROM @tempTable)
END