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

---根據實際業務進行邏輯處理後插入動態表

以上就是精品為大家提供的關於sql server動態存儲過程按日期保存數據的相關內容，希望能夠幫助到大家。