; 为了方便数据库的存储、数据的备份、恢复，在实际建库中，我们希望能够根据具体的年月建立数据表，例如，事件表我们可以建立为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
============================================================

刚毕业,做软件，初学使用存储过程。

标签：