Example of SQL Server dynamic stored procedure saving data by date

Time:2021-7-2

In the project, a large amount of data information is often saved to the database. If only one table is used to save the data, it is certainly not realistic. The preferred solution is to establish a dynamic table by date to save the data. Without changing the code of saving method, dynamic stored procedure is the first choice. Date calculation is carried out in SQL server stored procedure, and the efficiency of creating table by date is the highest

-----SQL statement:

ALTER proc [dbo].[EventInsert]
@Chrtagdata varchar (50), -- No
@intEData int,
@chrJZData varchar(50),
@intDYData int,
@intXHData int,
@createdata datetime,
@Chrtype varchar (1) -- query conditions
as
begin

declare @chrTitle varchar(1000)
declare @chrSql nvarchar(4000)
declare @chrdate varchar(50)
Declare @ chrmetabname varchar (50) -- create new alarm table name every day
Declare @ chrsendtabname varchar (50) -- daily new message pop-up new table name
Declare @ chrsockdatatabname varchar (50) -- new table name of daily raw data

set @chrdate =replace(convert(varchar(10),getdate(),120),'-','')

set @chrMetabname='SocketMe'[email protected]
set @chrSendtabname='MessSend'[email protected]
set @chrSockDatatabname='SockData'[email protected]

if isnull(@chrtype,'')=''
begin
return
end

select @chrTitle=CategoryTitle from EventCategory where [email protected]

----New daily information simulation table 1

set @chrsql= '
if not exists(select 1 from sysobjects where name='''[email protected]+''' and type=''U'')
begin
CREATE TABLE '[email protected]+'(
SMeID int IDENTITY(1,1) primary key,
tabname varchar(50),
TagData varchar(50),
TagDataMe varchar(500),
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)

--------New information simulation table 2------------

set @chrsql= '
if not exists(select 1 from sysobjects where name='''[email protected]+''' and type=''U'')
begin
CREATE TABLE '[email protected]+'(
MessID int IDENTITY(1,1) primary key,
TabName varchar(50),
TabPrID int,
MessTitle varchar(500),
TagData varchar(50),
TagDataMe varchar(1000),
Content varchar(2000),
Type varchar(1),
CreateDate datetime
)
end
'
--print @chrsql 
exec(@chrsql)

-----The data that meets the conditions judged by the simulation environment is inserted----------------------

set @chrsql= '
if not exists(select 1 from '[email protected]+' whereTagData='''[email protected]+''' and type='''[email protected]+''' and IfClose=''0'')
begin

--Insert table 1

insert into '[email protected]+' (tabname,TagData,TagDataMe,Content,
JZData,EData,DYData,XHData,Type,IfClose,CreateDate,State)

--Analog data

select '''[email protected]+''','''[email protected]+''',dbo.funTagDataMeget_all('''[email protected]+'''),
''' [email protected]+ 'location: [' + dbo. Fungetevenaddget (') [email protected]+ ''')+'']'',''' [email protected]+ ''','''+CAST(@intEData as varchar)+''','''+CAST(@intDYData as varchar)+''','''+CAST(@intXHData as varchar)+''',
'''[email protected]+''',''0'',getdate(),''0''

----Dbo. Fungetevenaddget is a custom function

declare @intSMeID int
declare @chrtempdate varchar(50)
set @intSMeID [email protected]@identity 

delete '[email protected]+' whereTagData='''[email protected]+''' andtype='''[email protected]+'''

---Insert table 2

insert into '[email protected]+' (TabName,TabPrID,MessTitle,TagData,Content,Type,CreateDate)
select '''[email protected]+''',@intSMeID,dbo.funTagDataMeget_all('''[email protected]+''')+'''[email protected]+''','''[email protected]+''',
dbo.funTagDataMeget_ all(''' [email protected]+ ''')+''' [email protected]+ 'location: [' + dbo. Fungetevenaddget (') [email protected]+ ''')+'']'',''' [email protected]+ ''',getdate()

end 
' 
print @chrsql
exec(@chrsql)

end

---Insert dynamic table after logical processing according to actual business