sql server靜態存儲進程按日期保留數據示例。本站提示廣大學習愛好者：（sql server靜態存儲進程按日期保留數據示例）文章只能為提供參考，不一定能成為您想要的結果。以下是sql server靜態存儲進程按日期保留數據示例正文
在項目中常常有年夜量數據信息保留到數據庫，如只用一張表保留那確定不實際，首選處理計劃為按日期樹立靜態表來保留數據。在不轉變保留方法的代碼的情形下，用靜態存儲進程是首選，在sql server存儲進程中停止日期盤算，按日期建表效力最高，上面就公司項目標部門靜態存儲進程粘貼出來：
-----sql語句：
ALTER proc [dbo].[EventInsert]
@chrTagData varchar(50), --編號
@intEData int,
@chrJZData varchar(50),
@intDYData int,
@intXHData int,
@createdata datetime,
@chrtype varchar(1) --查詢前提
as
begin
declare @chrTitle varchar(1000)
declare @chrSql nvarchar(4000)
declare @chrdate varchar(50)
declare @chrMetabname varchar(50) --逐日新建報警新表名
declare @chrSendtabname varchar(50) --逐日新建新聞彈出框新表名
declare @chrSockDatatabname varchar(50) --逐日原始數據新表名
set @chrdate =replace(convert(varchar(10),getdate(),120),'-','')
set @chrMetabname='SocketMe'+@chrdate
set @chrSendtabname='MessSend'+@chrdate
set @chrSockDatatabname='SockData'+@chrdate
if isnull(@chrtype,'')=''
begin
return
end
select @chrTitle=CategoryTitle from EventCategory where CategoryID=@chrtype
----新建逐日信息模仿表1
set @chrsql= '
if not exists(select 1 from sysobjects where name='''+@chrMetabname+''' and type=''U'')
begin
CREATE TABLE '+@chrMetabname+'(
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)
--------新建信息模仿表2------------
set @chrsql= '
if not exists(select 1 from sysobjects where name='''+@chrSendtabname+''' and type=''U'')
begin
CREATE TABLE '+@chrSendtabname+'(
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)
-----模仿情況 斷定相符前提的數據則拔出----------------------
set @chrsql= '
if not exists(select 1 from '+@chrMetabname+' whereTagData='''+@chrTagData+''' and type='''+@chrtype+''' and IfClose=''0'')
begin
--拔出表一
insert into '+@chrMetabname+' (tabname,TagData,TagDataMe,Content,
JZData,EData,DYData,XHData,Type,IfClose,CreateDate,State)
--模仿數據
select '''+@chrMetabname+''','''+@chrTagData+''',dbo.funTagDataMeget_all('''+@chrTagData+'''),
'''+@chrTitle+',地位:[''+dbo.funGetEvenAddget('''+@chrJZData+''')+'']'','''+@chrJZData+''','''+CAST(@intEData as varchar)+''','''+CAST(@intDYData as varchar)+''','''+CAST(@intXHData as varchar)+''',
'''+@chrtype+''',''0'',getdate(),''0''
----dbo.funGetEvenAddget 為自界說函數
declare @intSMeID int
declare @chrtempdate varchar(50)
set @intSMeID =@@identity
delete '+@chrSendtabname+' whereTagData='''+@chrTagData+''' andtype='''+@chrtype+'''
---拔出表二
insert into '+@chrSendtabname+' (TabName,TabPrID,MessTitle,TagData,Content,Type,CreateDate)
select '''+@chrMetabname+''',@intSMeID,dbo.funTagDataMeget_all('''+@chrTagData+''')+'''+@chrTitle+''','''+@chrTagData+''',
dbo.funTagDataMeget_all('''+@chrTagData+''')+'''+@chrTitle+',地位:[''+dbo.funGetEvenAddget('''+@chrJZData+''')+'']'','''+@chrtype+''',getdate()
end
'
print @chrsql
exec(@chrsql)
end
---依據現實營業停止邏輯處置後拔出靜態表
