贴几个最近写的几个sp

最新推荐文章于 2026-02-23 00:44:52 发布

原创最新推荐文章于 2026-02-23 00:44:52 发布 · 1.1k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#object #go #null #date #360

SQL Server 专栏收录该内容

1 篇文章

订阅专栏

--1.每天创建第二天的表，删除三个月前的表

/*----------------------------------------------------------------------------

Author ： HsuChong

Date ： 2007-08-25 15:20

Desc ： uspCreateTomorrowTable

------------------------------------------------------------------------------*/

CREATE PROCEDURE uspCreateTomorrowTable

@sOriginalTableName sysname

BEGIN

SET NOCOUNT ON ;

DECLARE @sExecSQL varchar(8000),

@sNewTableName sysname,

@sOldTableName sysname,

@tempDate char(8);

BEGIN TRY

--创建明天的表

SET @tempDate = CONVERT(CHAR(8),GETDATE(),112);

SET @sNewTableName = @sOriginalTableName + @tempDate;

SET @sExecSQL = 'IF NOT EXISTS(SELECT 1 FROM sysobjects '

+ ' WHERE ID = OBJECT_ID('''+@sNewTableName+'''))'

+ ' SELECT * INTO ' + @sNewTableName

+ ' FROM '+ @sOriginalTableName +' WHERE 1<> 1';

EXEC(@sExecSQL);

--删除三个月(90天)前的表

SET @tempDate = CONVERT(CHAR(8),GETDATE()-90,112);

SET @sOldTableName = @sOriginalTableName + @tempDate;

SET @sExecSQL = 'IF EXISTS(SELECT 1 FROM sysobjects '

+ ' WHERE ID = OBJECT_ID(''' + @sOldTableName + '''))'

+ ' DROP TABLE ' + @sOldTableName ;

EXEC(@sExecSQL);

END TRY

BEGIN CATCH

EXECUTE dbo.uspLogError;

END CATCH

END;

--2.联合查询两个表中的数据

/*-----------------------------------------------------------------------------------

Author ： HsuChong

Date ： 2007-08-24

Desc ： uspGetPlanetDataEX

----------------------------------------------------------------------------------*/

CREATE PROCEDURE uspGetPlanetDataEX

@VehicleSN varchar(20) = NULL, -- 车机SN

@BeginTime varchar(50) = NULL, -- 开始时间

@EndTime varchar(50) = NULL, -- 结束时间

@FiterNavigation bit = 1, -- 是否过滤信号弱数据：1=过滤,0=不过滤

@FiterSpeed bit = 1, -- 是否过滤0速度数据：1=过滤,0=不过滤

@FiterInvalidData bit = 1 -- 是否过滤非法数据(速度超过107海里/小时,方向大于不在0-360范围内等)

-- 1=过滤,0=不过滤

BEGIN

SET NOCOUNT ON;

DECLARE @sExecSQL varchar(4000),

@sMainSQL varchar(1000),

@sCondition varchar(4000),

@sOriginalTableName sysname,

@sTableName1 sysname,

@sTableName2 sysname,

@beginDate datetime,

@EndDate datetime,

@tempDate char(8),

@DayCount int;

BEGIN TRY

SET @sOriginalTableName = 'PlanetData';

SET @sMainSQL = 'SELECT SystemTime,PlanetTime,'

+ ' Longitude,Latitude,Speed,Direction,'

+ ' OverSpeed,LoadedOn,PowerOn,IsNavigation '

+ ' FROM ';

SET @beginDate = CAST(@BeginTime AS datetime);

SET @EndDate = CAST(@EndTime AS datetime);

SET @DayCount = DATEDIFF(DAY,@BeginDate,@EndDate);

--只允许查询两天内的数据

IF @DayCount > 1

RETURN;

SET @tempDate = CONVERT(CHAR(8),@beginDate,112);

SET @sTableName1 = @sOriginalTableName + @tempDate;

SET @tempDate = CONVERT(CHAR(8),@EndDate,112);

SET @sTableName2 = @sOriginalTableName + @tempDate;

SET @sCondition = ' AND (VehicleSN ='+ @VehicleSN +')';

IF @FiterNavigation = 1

SET @sCondition = @sCondition + ' AND (IsNavigation=1)' ;

IF @FiterSpeed = 1

SET @sCondition = @sCondition + ' AND (Speed>0) ' ;

IF @FiterInvalidData = 1

SET @sCondition = @sCondition

+ ' AND (Direction BETWEEN 0 AND 360) '

+ ' AND (Speed BETWEEN 0 AND 107) '

+ ' AND (Longitude > 0) AND (Latitude >0)';

SET @sCondition = @sCondition ;

IF @DayCount > 0

BEGIN

SET @sExecSQL = @sMainSQL + @sTableName1

+ ' WHERE (SystemTime >= '''+ @BeginTime +''')' + @sCondition

+ ' UNION ALL '

+ @sMainSQL + @sTableName2

+ ' WHERE (SystemTime <= '''+ @EndTime +''')' + @sCondition;

END

ELSE

BEGIN

SET @sExecSQL = @sMainSQL + @sTableName1

+ ' WHERE (SystemTime BETWEEN '''

+ @BeginTime +''' AND '''+@EndTime+''')'+ @sCondition;

END;

--2007-08-27 之前还没有分表

IF @BeginTime <'2007-08-27'

BEGIN

SET @sExecSQL = @sMainSQL + @sOriginalTableName

+ ' WHERE (SystemTime BETWEEN '''

+ @BeginTime +''' AND '''+@EndTime+''')'+ @sCondition;

END;

SET @sExecSQL = @sExecSQL + ' ORDER BY SystemTime';

PRINT @sExecSQL; ---just for test only

EXEC(@sExecSQL);

END TRY

BEGIN CATCH

EXECUTE dbo.uspLogError;

END CATCH

END;

--3.创建分区表(MS SQL2005)

-- =================================================================

-- Author : HsuChong@hotmail.com

-- Create date: 2007-07-10

-- Description: Make Partitioned Tables

-- =================================================================

CREATE PROCEDURE uspMakePartitionedTables

@bExecute bit = 0

BEGIN

DECLARE @tempDate char(8),

@sSQL varchar(8000),

@sCRLF char(2),

@sTAB char(1),

@PartitionFunctionName varchar(50),

@PartitionSchemeName varchar(50),

@PartitionTableName varchar(50),

@PartitionLine1 varchar(50),

@PartitionLine2 varchar(50),

@PartitionLine3 varchar(50),

@PartitionLine4 varchar(50),

@PartitionLine5 varchar(50);

BEGIN TRY

SET @sTAB = char(9);

SET @sCRLF = char(13) + char(10);

SET @tempDate = CONVERT(char(8),getdate()+1,112);

SET @PartitionFunctionName = 'OneDayDateRangePFN'+@tempDate;

SET @PartitionSchemeName = 'OneDayDateRangePScheme'+@tempDate;

SET @PartitionTableName = 'GPSData'+@tempDate;

SET @PartitionLine1 = '''' + @tempDate + ' 05:59:59.997'+'''';

SET @PartitionLine2 = '''' + @tempDate + ' 10:59:59.997'+'''';

SET @PartitionLine3 = '''' + @tempDate + ' 14:59:59.997'+'''';

SET @PartitionLine4 = '''' + @tempDate + ' 18:59:59.997'+'''';

SET @PartitionLine5 = '''' + @tempDate + ' 23:59:59.997'+'''';

--1.Create the partition function

SET @sSQL = 'CREATE PARTITION FUNCTION ' + @PartitionFunctionName+'(datetime)'+@sCRLF;

SET @sSQL = @sSQL + 'AS'+@sCRLF;

SET @sSQL = @sSQL + 'RANGE LEFT FOR VALUES('+@sCRLF;

SET @sSQL = @sSQL + @sTAB + @PartitionLine1 + ',' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + @PartitionLine2 + ',' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + @PartitionLine3 + ',' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + @PartitionLine4 + ',' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + @PartitionLine5 + ')' + @sCRLF + @sCRLF;

--SET @sSQL = @sSQL + 'GO' + @sCRLF; -- I did not know why the use 'GO' will be syntax error.

--2.Create the partition scheme

SET @sSQL = @sSQL + 'CREATE PARTITION SCHEME ' + @PartitionSchemeName + @sCRLF;

SET @sSQL = @sSQL + 'AS'+@sCRLF;

SET @sSQL = @sSQL + 'PARTITION '+ @PartitionFunctionName + ' TO ('+ @sCRLF;

SET @sSQL = @sSQL + @sTAB + '[FG1], [FG2], [FG3], [FG4], [FG5], [PRIMARY])' + @sCRLF + @sCRLF;

--SET @sSQL = @sSQL + 'GO' + @sCRLF;

--3.Create the table

SET @sSQL = @sSQL + 'CREATE TABLE '+@PartitionTableName+ '('+@sCRLF;

SET @sSQL = @sSQL + @sTAB + 'TerminalID dbo.TerminalCode NOT NULL,'+@sCRLF;

SET @sSQL = @sSQL + @sTAB + 'SystemTime datetime NOT NULL DEFAULT (getdate()),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'GPSTime datetime NOT NULL DEFAULT(getdate()),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'IsNavigation bit NOT NULL DEFAULT((0)),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'Longitude float NOT NULL DEFAULT((0)),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'Latitude float NOT NULL DEFAULT((0)),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'Speed smallint NOT NULL DEFAULT((0)),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'LoadedOn bit NULL DEFAULT((0)),' + @sCRLF;

SET @sSQL = @sSQL + @sTAB + 'PowerOn bit NULL DEFAULT((0))' + @sCRLF;

SET @sSQL = @sSQL + ') ON '+ @PartitionSchemeName + '(SystemTime)'+ @sCRLF + @sCRLF;

--SET @sSQL = @sSQL + 'GO' + @sCRLF;

--4.Create the clustered indexes as Primary keys

SET @sSQL = @sSQL + 'ALTER TABLE '+ @PartitionTableName+@sCRLF;

SET @sSQL = @sSQL + 'ADD CONSTRAINT '+ @PartitionTableName + 'PK' + @sCRLF;

SET @sSQL = @sSQL + 'PRIMARY KEY CLUSTERED (SystemTime,TerminalID)' + @sCRLF;

SET @sSQL = @sSQL + 'ON '+ @PartitionSchemeName + '(SystemTime)' + @sCRLF;

--SET @sSQL = @sSQL + 'GO' + @sCRLF;

PRINT @sSQL;

IF @bExecute = 1

EXEC (@sSQL);

END TRY

BEGIN CATCH

EXECUTE dbo.uspLogError;

END CATCH;

END;

--Just for test

--EXEC uspMakePartitionedTables

--or

--EXEC uspMakePartitionedTables 1