sql 游标系统表

 [广州]fm(309148970)  16:56:41
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure dbo.fm_update_basename
 @basetable varchar(100),
 @columlist varchar(255),
 @basecolum varchar(100)
as

--－－－调试用－－－
set @columlist='Product_Name','Product_code','reg_unit'

------------------

--声明一个游标
DECLARE MyCURSOR CURSOR FOR
select object_NAme(id) from syscolumns
where lower(name) in ('Product_Name','Product_code','reg_unit') and id in (select id from sysobjects where xtype = 'U' and lower(object_name(id))<>'TSS_Product_Info')
group by object_NAme(id)
having count(object_NAme(id))=3

--打开游标
open MyCURSOR

--声明三个变量
declare @tabname varchar(50)
declare @cou int
declare @count int

--循环移动
fetch next from MyCURSOR into @tabname

while(@@fetch_status=0)
    begin
   exec('update '+@tabname+' set '+@tabname+'.Product_Name = TSS_Product_Info.Product_Name ,'+@tabname+'.reg_unit = TSS_Product_Info.reg_unit from '+@tabname+',TSS_Product_Info where (TSS_Product_Info.Product_Code= '+@tabname+'.Product_Code)')
    if @@rowcount > 0
     print '更新了'+@tabname +'表'
       fetch next from MyCURSOR into @tabname
    end

--关闭游标
close MyCURSOR
deallocate MyCURSOR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO