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 ‘[email protected]+‘ set ‘[email protected]+‘=‘‘‘[email protected]+‘‘‘ where ‘[email protected]+‘=‘‘‘[email protected]+‘‘‘‘
exec(@strsql)
FETCH NEXT FROM ChangeInvCodeCursor INTO @tablecolumn1,@tablename1
END
--關閉遊標
CLOSE ChangeInvCodeCursor
DEALLOCATE ChangeInvCodeCursor

sqlserver 遊標