sql server动态存储过程按日期保存数据示例
在项目中经常有大量数据信息保存到数据库，如只用一张表保存那肯定不现实，首选解决方案为按日期建立动态表来保存数据。在不改变保存方式的代码的情况下，用动态存储过程是首选，在sqlserver存储过程中进行日期计算，按日期建表效率最高，下面就公司项目的部分动态存储过程粘贴出来：
-----sql语句：
ALTERproc[dbo].[EventInsert]
@chrTagDatavarchar(50),--编号
@intEDataint,
@chrJZDatavarchar(50),
@intDYDataint,
@intXHDataint,
@createdatadatetime,
@chrtypevarchar(1)--查询条件
as
begin
declare@chrTitlevarchar(1000)
declare@chrSqlnvarchar(4000)
declare@chrdatevarchar(50)
declare@chrMetabnamevarchar(50)--每日新建报警新表名
declare@chrSendtabnamevarchar(50)--每日新建消息弹出框新表名
declare@chrSockDatatabnamevarchar(50)--每日原始数据新表名
set@chrdate=replace(convert(varchar(10),getdate(),120),'-','')
set@chrMetabname='SocketMe'+@chrdate
set@chrSendtabname='MessSend'+@chrdate
set@chrSockDatatabname='SockData'+@chrdate
ifisnull(@chrtype,'')=''
begin
return
end
select@chrTitle=CategoryTitlefromEventCategorywhereCategoryID=@chrtype
----新建每日信息模拟表1
set@chrsql='
ifnotexists(select1fromsysobjectswherename='''+@chrMetabname+'''andtype=''U'')
begin
CREATETABLE'+@chrMetabname+'(
SMeIDintIDENTITY(1,1)primarykey,
tabnamevarchar(50),
TagDatavarchar(50),
TagDataMevarchar(500),
PcountintNULL,
Contentvarchar(5000),
UserIDintNULL,
JZDatavarchar(50),
EDataint,
DYDataint,
XHDataint,
Typevarchar(1),
Statevarchar(1),
IfClosevarchar(1),
CloseDatedatetime,
CreateDatedatetime,
)
end
'
--print@chrsql
exec(@chrsql)
--------新建信息模拟表2------------
set@chrsql='
ifnotexists(select1fromsysobjectswherename='''+@chrSendtabname+'''andtype=''U'')
begin
CREATETABLE'+@chrSendtabname+'(
MessIDintIDENTITY(1,1)primarykey,
TabNamevarchar(50),
TabPrIDint,
MessTitlevarchar(500),
TagDatavarchar(50),
TagDataMevarchar(1000),
Contentvarchar(2000),
Typevarchar(1),
CreateDatedatetime
)
end
'
--print@chrsql
exec(@chrsql)
-----模拟环境判断符合条件的数据则插入----------------------
set@chrsql='
ifnotexists(select1from'+@chrMetabname+'whereTagData='''+@chrTagData+'''andtype='''+@chrtype+'''andIfClose=''0'')
begin
--插入表一
insertinto'+@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(@intEDataasvarchar)+''','''+CAST(@intDYDataasvarchar)+''','''+CAST(@intXHDataasvarchar)+''',
'''+@chrtype+''',''0'',getdate(),''0''
----dbo.funGetEvenAddget为自定义函数
declare@intSMeIDint
declare@chrtempdatevarchar(50)
set@intSMeID=@@identity
delete'+@chrSendtabname+'whereTagData='''+@chrTagData+'''andtype='''+@chrtype+'''
---插入表二
insertinto'+@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
---根据实际业务进行逻辑处理后插入动态表