为了方便数据库的存储、数据的备份、恢复，在实际建库中，我们希望能够根据具体的年月建立数据表，例如，事件表我们可以建立为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)[email protected]
return (@chvtablename)
end
go
set quoted_identifier off
go
set ansi_nulls on
go
============================================================
刚毕业,做软件，初学使用存储过程。
菜鸟学堂：
