写的比较好的存储过程备份下(时间块的比较)

USE ...
GO
/****** Object:  StoredProcedure [Dzwl].[Get_MsTrace]    Script Date: 2012/10/26 16:09:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
/**
EXEC [Dzwl].[Get_MsTrace] '','',''
*/
ALTER PROCEDURE  [Dzwl].[Get_MsTrace]
	@IMSI NVARCHAR(15),
	@IMEI NVARCHAR(15),
	@Deviceno NVARCHAR(14)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @MsIntervalTime INT
	 SELECT @MsIntervalTime=CfgValue 
		FROM [DZWL_V2].[Dzwl].[SysCfg] WITH(NOLOCK)
		WHERE CfgName='MsIntervalTime'  

	CREATE TABLE #TempTimeInterval
	(
		TimeCeiling DATETIME NOT NULL,
		TimeBottom DATETIME NOT NULL
		CONSTRAINT PK_TimeInterval PRIMARY KEY 
		(
			TimeCeiling
		)
	)


	DECLARE CURTEMP CURSOR
		FOR 
		SELECT  
		DISTINCT
		Capture_Time
			FROM [Dzwl].[UserCardData] U WITH(NOLOCK)
	DECLARE @Capture_Time DATETIME

	OPEN CURTEMP
	FETCH NEXT FROM CURTEMP INTO @Capture_Time
	WHILE @@fetch_status=0
	BEGIN
		DECLARE @Exists1 INT=0,@Exists2 INT=0
		IF EXISTS (SELECT 1 FROM #TempTimeInterval WHERE TimeCeiling<=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)
													AND  TimeBottom>=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time))
		SET @Exists1=1
		IF EXISTS (SELECT 1 FROM #TempTimeInterval WHERE TimeCeiling<=DATEADD(SS,@MsIntervalTime,@Capture_Time)
													AND  TimeBottom>=DATEADD(SS,@MsIntervalTime,@Capture_Time))
		SET @Exists2=1
		IF @Exists1=0 AND @Exists2=0
		INSERT INTO #TempTimeInterval(TimeCeiling,TimeBottom)
		VALUES(DATEADD(SS,-1*@MsIntervalTime,@Capture_Time),DATEADD(SS,@MsIntervalTime,@Capture_Time))
		IF @Exists1=1 AND @Exists2=1
		BEGIN
			UPDATE #TempTimeInterval
			SET TimeBottom=(SELECT TimeBottom FROM #TempTimeInterval WHERE TimeCeiling<=DATEADD(SS,@MsIntervalTime,@Capture_Time)
																		AND TimeBottom>=DATEADD(SS,@MsIntervalTime,@Capture_Time))
			WHERE TimeCeiling<=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)
				AND TimeBottom>=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)

				DELETE #TempTimeInterval 
				WHERE TimeCeiling<=DATEADD(SS,@MsIntervalTime,@Capture_Time)
				AND TimeBottom>=DATEADD(SS,@MsIntervalTime,@Capture_Time)
		END
	 
		IF @Exists1=1 AND @Exists2=0
		BEGIN
			UPDATE #TempTimeInterval
			SET TimeBottom=DATEADD(SS,@MsIntervalTime,@Capture_Time)
			WHERE TimeCeiling<=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)
			AND  TimeBottom>=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)
		END   

		IF @Exists1=0 AND @Exists2=1
		BEGIN
			UPDATE #TempTimeInterval
			SET TimeCeiling=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)
			WHERE TimeCeiling<=DATEADD(SS,@MsIntervalTime,@Capture_Time)
			AND  TimeBottom>=DATEADD(SS,@MsIntervalTime,@Capture_Time)
		END   

		FETCH NEXT FROM CURTEMP INTO @Capture_Time
	END
	CLOSE CURTEMP
	DEALLOCATE CURTEMP
		
  SELECT DISTINCT
       M.[Id]
      ,M.[MsId]
      ,M.[IMSI]
      ,M.[IMEI]
      ,M.[Deviceno]
      ,M.[CrateDateTime]
      ,M.[RDAddress]
      ,M.[RDIp]
      ,M.[RDLat]
      ,M.[RDLon]
   FROM [Dzwl].[MsTrace] M WITH(NOLOCK) 
   INNER JOIN #TempTimeInterval U WITH(NOLOCK)
	   ON M.CrateDateTime >=U.TimeCeiLing
	   AND  M.CrateDateTime <=U.TimeBottom
   WHERE (@IMSI = '' OR [IMSI] LIKE '%' + @IMSI + '%') 
   AND (@IMEI = '' OR [IMEI] LIKE '%'+@IMEI+'%') 
   AND (@Deviceno = '' OR [Deviceno] LIKE '%'+@Deviceno+'%') 
   ORDER BY [Id] DESC
   
END