批量更新数据库中某字段的数值
CREATE PROCEDURE [dbo].[Sp_replace_field_value](@fieldName VARCHAR(90), @oldValue VARCHAR(90), @newValue VARCHAR(90)) AS DECLARE @tableName VARCHAR(100) DECLARE @sqlStmt VARCHAR(100) BEGIN DECLARE tablenamecursor CURSOR FOR SELECT col.table_name FROM information_schema.columns col, information_schema.tables tab WHERE col.table_name = tab.table_name AND tab.table_type = ‘BASE TABLE‘ AND col.column_name = @fieldName ORDER BY col.table_name OPEN tablenamecursor WHILE 1 = 1 BEGIN FETCH next FROM tablenamecursor INTO @tableName IF @@fetch_status <> 0 BREAK ELSE BEGIN SET @sqlStmt = ‘update ‘ + @tableName + ‘ set ‘ + @fieldName + ‘ = ‘ + @newValue + ‘ where ‘ + @fieldName + ‘ = ‘ + @oldValue PRINT @sqlStmt EXEC(@sqlStmt) END END CLOSE tablenamecursor DEALLOCATE tablenamecursor END
本文出自 “技术永无止境未来事在人为” 博客,谢绝转载!
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。