In order to facilitate the database storage, data backup, recovery, in the actual construction library, we hope to establish a data sheet according to the specific annual month, for example, the event table can be established to EV_YYYYMM (YYYY year, mm is month), use The stored procedure can solve the dynamic building form. (The source code is as follows:) ************************************************** *********** Storage procedure original code ******************************************** *******************
==== Generate the stored procedure prcreatedateTable === set quoted_identifier off goset ANSI_NULLS OFF GO
Alter Procedure PrcreatedTetableAs - Initialization DecLare @INTERRORCODE INT, - Error number, successfully display 0 @dtmcheckday datetime, - System Current Time @Str Varchar (40), @ Substr VARCHAR (10), @chrnsql nvarchar (1000) - SQL query DECLARE @chvSuffixTableName varchar (50), - the name of the table date suffix @ chvFinalTableName1 varchar (40), - to be detected table name @ 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) --- Formatted monthly to custom functions
SELECT @ chvFinalTableName1 = 'EV_' '_' @chvSuffixTableName - query whether @chvTableName_XXXXXX (years), that @chvFinalTableName table, if not then establish BEGIN IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = @ chvFinalTableName1 AND Xtype = 'u') - Event Table Begin Select @ chrnsql = 'Create Table [DBO]. [' @ chvfinaltablename8 '] (' '[evid] [char] (12) collate chinese_prc_ci_as not null,' '[Startime] 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] [varcha] (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, ' ' connectiont ' @ ChvfinalTableName8 '_ PK' 'Primary Key CLUSTERED ([EVID]) ON [PRIMARY] ' ') ON [PRIMARY] 'EXEC sp_ExecuteSql @chrnSQL ENDEND; GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO === obtain custom function date ==== SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS On Go
ALTER FUNCTION fnFormatDate_Month (@DATE datetime) RETURNS varchar (50) AS BEGIN declare @intDateYear int, --- require years @intDateMonthNo int --- processing of data need to be addressed in February declare @chvMonthNo varchar (10) data, @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 @ chavtablename = Convert (varchar (4), @ INTDATEYAR) @ chamonthno return (@chvtablename) end
Goset quoted_identifier off goset ansi_nulls on go =========================================== ================= Just graduated, do software, beginners use stored procedures.
