SQL server Dynamic Stored Procedure saves data by date example, server Stored Procedure

SQL server Dynamic Stored Procedure saves data by date example, server Stored Procedure

In projects, a large amount of data information is often stored in the database. It is unrealistic to save only one table. The preferred solution is to create a dynamic table by date to save data. Without changing the storage method code, using a dynamic storage process is the first choice. In the SQL server storage process, date calculation is performed, and table creation by date is the most efficient, the following describes some of the dynamic stored procedures of the company's projects:

----- SQL statement: ALTER proc [dbo]. [EventInsert] @ chrTagData varchar (50), -- number @ intEData int, @ chrJZData varchar (50), @ intDYData int, @ intXHData int, @ createdata datetime, @ chrtype varchar (1) -- Query condition asbegindeclare @ chrTitle varchar (1000) declare @ chrSql nvarchar (4000) declare @ chrdate varchar (50) declare @ chrMetabname varchar (50) -- create an alarm table on a daily basis. The new table name is declare @ chrSendtabname varchar (50). The new table name is declare @ chrSockDatatabname varchar (50) -- new table name of the daily raw data: set @ chrdate = replace (convert (varchar (10), getdate (), 120 ),'-','') set @ chrMetabname = 'etetme' + @ chrdateset @ chrSendtabname = 'messsend' + @ chrdateset @ chrSockDatatabname = 'sockdata' + @ chrdateif isnull (@ chrtype ,'') = ''ininreturnendselect @ chrTitle = CategoryTitle from EventCategory where CategoryID = @ chrtype ---- create a daily information Simulation Table 1 set @ chrsql = 'If not exists (select 1 from sysobjects where name ='' '+ @ chrMetabname + ''' and type = ''u '') beginCREATE TABLE '+ @ chrMetabname +' (SMeID int IDENTITY (500) primary key, tabname varchar (50), TagData varchar (50), TagDataMe varchar (), Pcount int NULL, content varchar (5000), UserID int NULL, JZData varchar (50), EData int, DYData int, XHData int, Type varchar (1), State varchar (1 ), ifClose varchar (1), CloseDate datetime, CreateDate datetime,) end' -- print @ chrsql exec (@ chrsql) -------- create information Simulation Table 2 ------------ set @ chrsql = 'If not exists (select 1 from sysobjects where name = ''' + @ chrSendtabname + ''' and type = 'U' ') beginCREATE TABLE '+ @ chrSendtabname +' (MessID int IDENTITY (500) primary key, TabName varchar (50), TabPrID int, MessTitle varchar (), TagData varchar (50 ), tagDataMe varchar (1000), Content varchar (2000), Type varchar (1), CreateDate datetime) end' -- print @ chrsql exec (@ chrsql) ----- when the simulated environment determines that the data meets the conditions, insert -------------------- set @ chrsql = 'If not exists (select 1 from '+ @ chrMetabname + 'wheretagdata = ''' + @ chrTagData +'' and type = ''' + @ chrtype + ''' and IfClose = ''0 '') begin -- insert TABLE 1 insert into '+ @ chrMetabname +' (tabname, TagData, TagDataMe, Content, JZData, EData, DYData, XHData, Type, IfClose, CreateDate, State) -- simulate data select ''' + @ chrMetabname + ''', ''' + @ chrTagData + ''', dbo. funTagDataMeget_all (''' + @ chrTagData + '''), ''' + @ chrTitle + ', Location: [''+ dbo. funGetEvenAddget (''' + @ chreceivzdata + ''') + '']'', ''' + @ chreceivzdata + ''', ''' + CAST (@ intEData as varchar) + ''', ''' + CAST (@ intDYData as varchar) + ''', ''' + CAST (@ intXHData as varchar) + ''', ''' + @ chrtype + ''', ''0'', getdate (), ''0'' ---- dbo. funGetEvenAddget is the custom function declare @ intSMeID intdeclare @ chrtempdate varchar (50) set @ intSMeID = @ identity delete '+ @ chrSendtabname + 'wheretagdata = ''' + @ chrTagData + ''' andtype = ''' + @ chrtype + ''' --- insert table 2 insert into '+ @ chrSendtabname +' (TabName, tabPrID, MessTitle, TagData, Content, Type, CreateDate) select ''' + @ chrMetabname + ''', @ intSMeID, dbo. funTagDataMeget_all (''' + @ chrTagData + ''') + ''' + @ chrTitle + ''', ''' + @ chrTagData + ''', dbo. funTagDataMeget_all (''' + @ chrTagData + ''') + ''' + @ chrTitle + ', Location: [''+ dbo. funGetEvenAddget (''' + @ chreceivzdata + ''') + '']'', ''' + @ chrtype + ''', getdate () end 'print @ chrsqlexec (@ chrsql) end --- Perform logic processing based on the actual business and insert a dynamic table

How to use dynamic date for sqlserver2000 stored procedure parameters

You can only think of this method:
Declare @ dateid datetime
Set @ dateid = getdate ()
Exec _ test @ dateid

How to use dynamic date for sqlserver2000 stored procedure parameters

SET @ youdate = getdate ();
Hope to adopt