56,677
社区成员
发帖
与我相关
我的任务
分享
Create PROCEDURE USP_CreateLogTables(in Pointer datetime,in Counts int)
begin
declare CountPointer int
declare DateString varchar(20)
declare TableName varchar(50)
declare ExecuteSQL varchar(50)
declare SQLTxt varchar(max)
Set CountPointer = Counts
Set SQLTxt=' CREATE TABLE dbo.Tracker_Log(
LogID bigint PRIMARY KEY auto_increment NOT NULL,
TrackerID varchar(50) NOT NULL,
LogTime datetime NOT NULL,
SessionId varchar(50) NOT NULL,
PassportID bigint NULL,
UserID bigint NULL,
ClientIP varchar(64) NULL,
ServerIP varchar(64) NULL,
CurrentUrl varchar(255) NULL,
ReferrerUrl varchar(255) NULL,
ClickLinkUrl varchar(255) NULL,
ClickLinkTitle nvarchar(200) NULL,
CurrentClientTime datetime NULL,
CurrentServerTime datetime NULL,
ClientServerTimeDif bigint Null,
ServerExecuteTime bigint NULL,
NetTransTime bigint NULL,
ServerResponseTime datetime NULL,
ClientLoadTime bigint NULL,
UserDwellTime bigint NULL
)'
While(CountPointer > 0)
Begin
Set DateString = Right(Cast(DateName(yyyy, Pointer) as varchar(4)), 2) + '_' +
Right('0' + Cast(Month(Pointer) as varchar(2)), 2) + '_' +
Right('0' + Cast(DatePart(day, Pointer) as varchar(2)), 2)
Set TableName = 'Tracker_Log_' + DateString
Select ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName)
execute(ExecuteSQL)
Set CountPointer = CountPointer - 1
Set Pointer = DateAdd(dd, CountPointer, getDate())
End
END