游标，逐步插入数据

USE [ODS_ZhenHe]
GO

/****** Object:  StoredProcedure [dbo].[proc_SelSQL]    Script Date: 2018/5/8 11:12:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO









create PROCEDURE [dbo].[proc_SelSQL]
@period datetime,
@num int,
@DatebaseName varchar(255)
AS
begin

begin try
    begin tran 
	declare @sql varchar(max)
	declare @name varchar(max)
	declare @deleteSql varchar(max)
	declare @insertSql varchar(max)
	declare @rqtype varchar(255)
	set @DatebaseName=rtrim(LTRIM(@DatebaseName))
	declare My_Cursor CURSOR for 
	   SELECT SQLStatement FROM [dbo].[SQL_Language]
	   WHERE  Status='1' 
	   order by orderid asc
	Open  My_Cursor
	while @@fetch_status=0
	begin
		 select @name=BTable FROM [dbo].[SQL_Language]
		   WHERE  Status='1' and SQLStatement=@sql
		  ---查询表的列名，取最先建立的日期
		  select @rqtype=b.name 
		  from sysobjects a,syscolumns b,systypes c where a.id=b.id
		  and a.xtype='U'
		  and a.name=@name
		  and c.name='datetime'
		  and b.xtype=c.xtype
		  and b.colid in( select min(b.colid)
				  from sysobjects a,syscolumns b,systypes c where a.id=b.id
				  and a.xtype='U'
				  and a.name=@name
				  and c.name='datetime'
				  and b.xtype=c.xtype ) 
		 ---给sql替换变量
		 set @sql=REPLACE(REPLACE(@sql,'@period',''''+convert(varchar(100),@period,121)+''''),'@num',@num)
		 set @sql=REPLACE(@sql,'@DatebaseName',@DatebaseName)
		 --set @deleteSql='delete from '+ @name + ' where  YEAR('+@rqtype+')=YEAR('+''''+convert(varchar(100),@period,121)+''''+') 
		 --and     MONTH('+@rqtype+')<=MONTH('+''''+convert(varchar(100),@period,121)+''''+')-1          
		 -- and  MONTH('+@rqtype+')>=MONTH('+''''+convert(varchar(100),@period,121)+''''+')-'+convert(varchar(10),@num)
		  set @deleteSql ='delete from  '+ @name + ' where      '+ @rqtype +'<'+''''+convert(varchar(100),@period,121)+''''+'
	and  '+@rqtype+'>=[dbo].[MGetNumMothFirstDay]('+''''+convert(varchar(100),@period,121)+''''+','+convert(varchar(10),@num)+')'
		  print @deleteSql
		  exec(@deleteSql)
		 set @insertSql= 'insert into ' + @name + '
		 '+@sql+''
		 print @insertSql
		  exec(@insertSql)
		 fetch next from My_Cursor into @sql
	end
		  Close My_Cursor
		  Deallocate My_Cursor
		  commit tran 

end try 
begin catch
     
     print  '错误：' +@@error 
     rollback tran 
end catch
end





GO