為了方便數(shù)據(jù)庫的存儲、數(shù)據(jù)的備份、恢復(fù)，在實際建庫中，我們希望能夠根據(jù)具體的年月建立數(shù)據(jù)表，例如，事件表我們可以建立為ev_yyyymm(yyyy為年份，mm為月份)，使用存儲過程可以解決動態(tài)建表。（源代碼如下：）
****************************************************
*** 存儲過程原碼 ***
****************************************************
====生成表的存儲過程prcreatedatetable===
set quoted_identifier off
go
set ansi_nulls off
go
alter procedure prcreatedatetable
as
--初始化
declare @interrorcode int, --錯誤號，成功顯示0
@dtmcheckday datetime, --系統(tǒng)當(dāng)前時間
@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) ---取格式化后的月用到自定義函數(shù)
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
===得到日期的自定義函數(shù)====
set quoted_identifier on
go
set ansi_nulls on
go
alter function fnformatdate_month (@date datetime)
returns varchar(50)
as
begin
declare @intdateyear int, ---需要處理數(shù)據(jù)的年份
@intdatemonthno int ---需要處理數(shù)據(jù)的月份
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
============================================================
剛畢業(yè),做軟件，初學(xué)使用存儲過程。
菜鳥學(xué)堂：
