sql server動(dòng)態(tài)存儲(chǔ)過程按日期保存數(shù)據(jù)示例

-----sql語句：

ALTER proc [dbo].[EventInsert]
@chrTagData varchar(50), --編號(hào)
@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) --每日新建報(bào)警新表名
declare @chrSendtabname varchar(50) --每日新建消息彈出框新表名
declare @chrSockDatatabname varchar(50) --每日原始數(shù)據(jù)新表名

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)

-----模擬環(huán)境 判斷符合條件的數(shù)據(jù)則插入----------------------

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)

--模擬數(shù)據(jù)

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 為自定義函數(shù)

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

---根據(jù)實(shí)際業(yè)務(wù)進(jìn)行邏輯處理后插入動(dòng)態(tài)表