create PROCEDURE [dbo].[Pro_ZT_SYS_LogInfo_clear]

@dt_end datetime --清理此日期之前的数据

AS

BEGIN

SET NOCOUNT ON;

declare @sqlstr varchar(8000)

declare @table_name varchar(30)

declare @theday datetime

set @theday = dateadd(month,-1,@dt_end)

set @table_name = 'ZT_SYS_LogInfo_'+ltrim(year(@theday-1))+substring(ltrim(month(@theday-1)+100),2,2)

set @sqlstr = '

CREATE TABLE [dbo].'+@table_name+'(

		[LogID] [int] IDENTITY(1,1) NOT NULL,

		[LogType] [varchar](50) NULL,

		[OperatorType] [varchar](50) NULL,

		[FuncName] [varchar](50) NULL,

		[OperatorInfo] [text] NULL,

		[LoginIP] [varchar](50) NULL,

		[CreateBy] [varchar](50) NULL,

		[CreateDate] [datetime] NULL,

		[LastUpdateBy] [varchar](50) NULL,

		[LastUpdateDate] [datetime] NULL,

		[Remark] [text] NULL,

	 CONSTRAINT [PK_'+@table_name+'] PRIMARY KEY NONCLUSTERED

	(

		[LogID] ASC

	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

'

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table_name) AND type in (N'U'))

exec(@sqlstr)

set @sqlstr = '

delete  from ZT_SYS_LogInfo

output deleted.*

into  '+@table_name+' ([LogID]

,[LogType]

,[OperatorType]

,[FuncName]

,[OperatorInfo]

,[LoginIP]

,[CreateBy]

,[CreateDate]

,[LastUpdateBy]

,[LastUpdateDate]

,[Remark])

where [CreateDate] < '''+convert(varchar(20),@theday)+''''

exec(@sqlstr)

END