sql 一段存储过程

ALTER PROCEDURE [dbo].[Sync_All_Materiel] 
 @where nvarchar(1000)=''
AS
 declare @sql nvarchar(1000)
 declare @registercode varchar(50)   
 declare @autoid varchar(20)
 declare @mtid varchar(20)
 declare @insertrow varchar(10)
 declare @updaterow varchar(10)
BEGIN  

 SET NOCOUNT  ON
 
 set @sql='declare cregistertable cursor '+
     'for '+
     'select registercode from  dbo.CRegister with(nolock) where 1=1 '+@where

 exec(@sql)

 open cregistertable  
 fetch next from cregistertable into @registercode
 while (@@FETCH_STATUS=0)
 begin
 
  if(@registercode='' or @registercode is null or replace(ltrim(rtrim(@registercode)),' ','')='')
  begin
   continue
  end 
  
  begin transaction
  
  begin try        --TRY存储过程

   set @sql='select @a=[mtid] from '+@registercode+'Basic_mTypes with(nolock) where tName = ''新增物料'''
   exec sp_executesql @sql,N'@a varchar(20) output',@mtid output
   if(@mtid is null)
   begin 

    set @sql='select @a=isnull(max(mTID),0)+1 from '+@registercode+'Basic_mTypes'
    exec sp_executesql @sql,N'@a varchar(20) output',@mtid output
        
    set @sql='insert into '+@registercode+'Basic_mTypes(mtID,tName,ParenttID,SynchTag,Contents,UpdateTAG)'+
       'values('+@mtid+',''新增物料'',0,0,''从柳汽同步过来的新物料'',1)'
       
    exec (@sql)
    
   end 
   
   set @sql='select @a=isnull(max(mid),0)+1 from '+@registercode+'Basic_Materiel'
   exec sp_executesql @sql,N'@a varchar(20) output',@autoid output
   
   --批量插入
   set @sql='insert into '+@registercode+'basic_materiel(mid,mtid,alterday,updatetag,carfatory,codesource,mnature,
        mcode,mname,mspec,mbrand,munit,mbarcode,spellcode,assemblycode,
        assemblyname,recover,[load],ersatz,englishname,isstop,contents2,
        jobprice) select (row_number() over(order by a.mid))+'+@autoid+' as mid,
        '+@mtid+' as mtid,a.alterday,a.updatetag, a.carfatory, a.codesource, a.mnature, a.mcode,
        a.mname, a.mspec, a.mbrand, a.munit, a.mbarcode, a.spellcode, a.assemblycode,
        a.assemblyname,cast(a.recover as int) recover,cast(a.[load] as int) [load],
        cast(a.ersatz as int)
        ersatz, a.englishname,cast(a.isstop as int) isstop,''1'' contents2,
        a.jobprice from LQ_Server.dbo.Basic_Materiel a /*基表*/
        where not exists(select mid from '+@registercode+'basic_materiel b
        where a.mcode=b.mcode and b.updatetag<>3)
        and a.updatetag <>3 '
        
   
   exec (@sql)
   
   select @insertrow=cast(@@rowcount as varchar)
   
   --批量更新
   set @sql='update '+@registercode+'Basic_Materiel set alterday=b.alterday,updatetag=b.updatetag,
      carfatory=b.carfatory,
      codesource=b.codesource,mnature=b.mnature,
      mcode=b.mcode,mspec=b.mspec,mbrand=b.mbrand,munit=b.munit,
      mbarcode=b.mbarcode,spellcode=b.spellcode,assemblycode=b.assemblycode,
      assemblyname=b.assemblyname,recover=cast(b.Recover as int),
      [load]=cast(b.[Load] as int),ersatz=cast(b.Ersatz as int),
      englishname=b.englishname,isstop=cast(b.IsStop as int),
      jobprice=b.jobprice from LQ_Server.dbo.Basic_Materiel b/*基表*/
      where '+@registercode+'Basic_Materiel.mcode=b.mcode
      and '+@registercode+'Basic_Materiel.updatetag <>3  and b.updatetag <>3
      and b.AlterDay>'+@registercode+'Basic_Materiel.AlterDay'
      
   
   exec (@sql)
   
   select @updaterow=cast(@@rowcount as varchar) 
      
   set @sql='select @a=count(*) from '+@registercode+'updatetag where
       tablename ='''+@registercode+'basic_materiel'''
      
   exec sp_executesql @sql,N'@a varchar(20) output',@autoid output
   
   if(@autoid=0)
   begin
    
    set @sql='insert into '+@registercode+'updatetag(tablename,cname,ccname,utime)
       values('''+@registercode+'basic_materiel'','''','''',
       convert(varchar(100), getdate(), 20))'
   end
   else
   begin
    set @sql='update '+@registercode+'updatetag set utime=convert(varchar(100), getdate(), 20)
       where tablename = '''+@registercode+'basic_materiel'''
   end
   
   exec(@sql)
   
   
   print '更新'+@registercode+'成功  新增'+@insertrow+'条  更新'+@updaterow+'条'
   commit transaction
  
  end try
  begin catch   
   rollback transaction
   print error_message()
  end catch
 
  --if(@@sqlerror)

  fetch next from cregistertable into @registercode
 end

 close cregistertable
 deallocate cregistertable
 
 return
END