开发中遇到多个表要设置区域REGIONCODE这个字段的更新,所以写了这个存储过程统一处理
CREATE PROCEDURE [dbo].[Sp_RegionCode_SD_value]
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 = 'REGIONCODE'
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 regioncode=''440606000000'' where regioncode=''449000000000'''
PRINT @sqlStmt
EXEC(@sqlStmt)
END
END
CLOSE tablenamecursor
DEALLOCATE tablenamecursor
END
exec [dbo].[Sp_RegionCode_SD_value]