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

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

============================================================

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航