应用存储过程按月建数据表

; 为了方便数据库的存储、数据的备份、恢复，在实际建库中，我们希望能够根据具体的年月建立数据表，例如，事件表我们可以建立为EV_yyyymm(yyyy为年份，mm为月份)，使用存储过程可以解决动态建表。（源代码如下：）*******************************************************;;;;;存储过程原码; *******************************************************

====生成表的存储过程prCreateDateTable===SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO

ALTER PROCEDURE prCreateDateTableAS --初始化;; 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表,如果没有则建立BEGINIF NOT;EXISTS (SELECT * FROM sysobjects WHERE NAME = @chvFinalTableName1 AND xtype='U');;;-- 事件表BEGINSELECT @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 @chrnSQLENDEND;GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO===得到日期的自定义函数====SET QUOTED_IDENTIFIER ON GOSET 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 ,2select; @chvTableName=convert(varchar(4),@intDateYear)+@chvMonthNoreturn (@chvTableName)END

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO============================================================刚毕业,做软件，初学使用存储过程。