USE ...GO/****** Object: StoredProcedure [Dzwl].[Get_MsTrace] Script Date: 2012/10/26 16:09:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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)ASBEGINSET NOCOUNT ONDECLARE @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 NULLCONSTRAINT PK_TimeInterval PRIMARY KEY (TimeCeiling))DECLARE CURTEMP CURSORFOR SELECT DISTINCTCapture_TimeFROM [Dzwl].[UserCardData] U WITH(NOLOCK)DECLARE @Capture_Time DATETIMEOPEN CURTEMPFETCH NEXT FROM CURTEMP INTO @Capture_TimeWHILE @@fetch_status=0BEGINDECLARE @Exists1 INT=0,@Exists2 INT=0IF EXISTS (SELECT 1 FROM #TempTimeInterval WHERE TimeCeiling<=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time)AND TimeBottom>=DATEADD(SS,-1*@MsIntervalTime,@Capture_Time))SET @Exists1=1IF EXISTS (SELECT 1 FROM #TempTimeInterval WHERE TimeCeiling<=DATEADD(SS,@MsIntervalTime,@Capture_Time)AND TimeBottom>=DATEADD(SS,@MsIntervalTime,@Capture_Time))SET @Exists2=1IF @Exists1=0 AND @Exists2=0INSERT INTO #TempTimeInterval(TimeCeiling,TimeBottom)VALUES(DATEADD(SS,-1*@MsIntervalTime,@Capture_Time),DATEADD(SS,@MsIntervalTime,@Capture_Time))IF @Exists1=1 AND @Exists2=1BEGINUPDATE #TempTimeIntervalSET 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=0BEGINUPDATE #TempTimeIntervalSET 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=1BEGINUPDATE #TempTimeIntervalSET 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_TimeENDCLOSE CURTEMPDEALLOCATE 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
