创建存储过程

 

Create PROCEDURE [dbo].[procGetData]
(
 @Days int 

)	
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	if @Days is null
    set @Days=1000000
    declare @sql nvarchar(2000);
    
	set  @sql = ' insert into tbcont select * from openquery([10.**.**.**], 
                 ''select * from [dbH].[dbo].viewTitle
                 where datediff(dd, ModifyDate, getdate()) <=' 
               + cast(@Days as varchar(10)) +' '')';
    exec sp_executesql @sql;  
	print @sql; 
	
	print 'step'; 
	set  @sql = 'select * into tbcon from openquery([10.**.**.**], 
                 '' select t1.* from '
               + ' [dbH].[dbo].[viewDetail] t1 ' 
               + ' inner join [dbH].[dbo].[viewTitle] t2 '
               + ' on t1.ID = t2.ID'
               + ' and t1.VersionID = t2.VersionID '
               + ' where datediff(dd, t2.ModifyDate, getdate()) <= '
               + cast(@Days as varchar(10)) + ' '')';                                  
    exec sp_executesql @sql;
    
    begin transaction;
    begin try

    --delete from tbcont
    --INSERT INTO [dbo].[tbcont
    --Update [dbo].[tbcont]

    commit transaction; 
    end try

    begin catch
	rollback transaction;  
    end catch;  
    END