新手求教:(目的:数据库有200多个表里面都含有code字段,以前code是6为,现在想在第三位后面加‘00‘)
DECLARE @name varchar(50)
DECLARE @sql varchar(8000)
DECLARE authors_cursor CURSOR FOR
select name from sysobjects where type = 'u' and id in (select id from syscolumns where name = 'code')
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='update table set code=left(code,3)+''00''+substring(code,3,3) '+@name
print (@sql)
exec(@sql)
FETCH NEXT FROM authors_cursor INTO @name
END
CLOSE authors_cursor
DEALLOCATE authors_cursor执行的时候始终抱table错,不知道这里应该怎么使用表,新手以前没有用过游标,对标不熟悉,麻烦高手执教。
DECLARE @name varchar(50)
DECLARE @sql varchar(8000)
DECLARE authors_cursor CURSOR FOR
select name from sysobjects where type = 'u' and id in (select id from syscolumns where name = 'code')
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='update table set code=left(code,3)+''00''+substring(code,3,3) '+@name
print (@sql)
exec(@sql)
FETCH NEXT FROM authors_cursor INTO @name
END
CLOSE authors_cursor
DEALLOCATE authors_cursor执行的时候始终抱table错,不知道这里应该怎么使用表,新手以前没有用过游标,对标不熟悉,麻烦高手执教。
--用stuff函数更好
set @sql='update '+@name+' set code=stuff(code,3,0,''00'') '
--------------------------------------
set @sql='update ' + @name +' set code=left(code,3)+''00''+substring(code,3,3) '