sqlserver 游标

DECLARE ChangeInvCodeCursor CURSOR
FOR SELECT A.name AS tablecolumn,C.name AS tablename FROM sys.columns A LEFT JOIN sys.types B ON A.user_type_id = B.user_type_id RIGHT JOIN SYS.tables C ON C.object_id=A.object_id WHERE A.name =‘CINVCODE‘OR A.name=‘INVCODE‘ ORDER BY C.NAME
--打开游标
OPEN ChangeInvCodeCursor
DECLARE @tablecolumn1 nvarchar(120)
DECLARE @tablename1 nvarchar(120)
DECLARE @strsql nvarchar(2048)
DECLARE @BeforeCode nvarchar(120)
DECLARE @afterCode nvarchar(120)
FETCH NEXT FROM ChangeInvCodeCursor INTO @tablecolumn1,@tablename1
WHILE @@FETCH_STATUS =0
BEGIN
--变化前存货编码
set @BeforeCode=‘0111‘
--变化后存货编码
set @afterCode=‘0222‘
set @strsql=‘update ‘+@tablename1+‘ set ‘+@tablecolumn1+‘=‘‘‘+@afterCode+‘‘‘ where ‘+@tablecolumn1+‘=‘‘‘+@BeforeCode+‘‘‘‘
exec(@strsql)
FETCH NEXT FROM ChangeInvCodeCursor INTO @tablecolumn1,@tablename1
END
--关闭游标
CLOSE ChangeInvCodeCursor
DEALLOCATE ChangeInvCodeCursor

sqlserver 游标

标签：