为了方便数据库的存储、数据的备份、恢复,在实际建库中,我们希望能够根据具体的年月建立数据表,例如,事件表我们可以建立为EV_yyyymm(yyyy为年份,mm为月份),使用存储过程可以解决动态建表。(源代码如下:)
****************************************************
*** 存储过程原码 ***
****************************************************
====生成表的存储过程prCreateDateTable===
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE prCreateDateTable
AS
--初始化
DECLARE @intErrorCode int, --错误号,成功显示0
@dtmCheckDay datetime, --系统当前时间
@str varchar(40),@SubStr varchar(10),
@chrnSQL nvarchar(1000) --SQL查询语句
DECLARE @chvSuffixTableName varchar(50), --表名称的日期后缀
@chvFinalTableName1 varchar(40), --待检测的表名称
@chvFinalTableName2 varchar(40),
@chvFinalTableName3 varchar(40),
@chvFinalTableName4 varchar(40),
@chvFinalTableName5 varchar(40),
@chvFinalTableName6 varchar(40),
@chvFinalTableName7 varchar(40),
@chvFinalTableName8 varchar(40)
SELECT @dtmCheckDay = getdate()
SELECT @chvSuffixTableName = dbo.fnFormatDate_month(@dtmCheckDay) ---取格式化后的月用到自定义函数
SELECT @chvFinalTableName1 = EV_ + _ + @chvSuffixTableName
--查询有无@chvTableName_XXXXXX(年月),即@chvFinalTableName表,如果没有则建立
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = @chvFinalTableName1 AND xtype=U) -- 事件表
BEGIN
SELECT @chrnSQL= CREATE TABLE [dbo].[+ @chvFinalTableName8 +](
+[EVID] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
+[StarTime] [datetime] NOT NULL ,
+[StarStake] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
+[EndStake] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
+[Direcation] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
+[EvType] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
+[EndTime] [datetime] NULL ,
+[Description] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
+[Advice] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
+[EconLoss] [int] NULL ,
+[DeathToll] [tinyint] NULL,
+ CONSTRAINT + @chvFinalTableName8 +_PK + PRIMARY KEY CLUSTERED ([EVID]) ON [PRIMARY]
+) ON [PRIMARY]
EXEC sp_ExecuteSql @chrnSQL
END
END;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
===得到日期的自定义函数====
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION fnFormatDate_Month (@DATE datetime)
RETURNS varchar(50)
AS
BEGIN
declare @intDateYear int, ---需要处理数据的年份
@intDateMonthNo int ---需要处理数据的月份
declare @chvMonthNo varchar(10) ,
@chvTableName varchar(50)
select @intDateYear = year(@DATE )
select @intDateMonthNo = month(@DATE)select @chvMonthNo=00+convert(varchar(2),@intDateMonthNo)
select @chvMonthNo=substring(@chvMonthNo,len(@chvMonthNo)-1 ,2
select @chvTableName=convert(varchar(4),@intDateYear)+@chvMonthNo
return (@chvTableName)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
============================================================