SQLSERVER根据字段和值查询和数据处理

SQLServer中 利用字段和值进行批量处理

关键字：存储过程，游标

步骤：1. 创建一个存储过程用来查询需要用到的表数据
步骤：2. 将查询到的值拼接成一个更新Update的SQL存放到表中
步骤：3. 利用游标处理SQL

实际代码

--创建存储过程
CREATE  PROCEDURE  [dbo].[SP_FindValueInDB]
(
     @value  VARCHAR (1024),
	 @where_column VARCHAR (100)
) 
AS
BEGIN
     SET  NOCOUNT  ON ;
     DECLARE  @sql  VARCHAR (1024) 
     DECLARE  @table  VARCHAR (64) 
     DECLARE  @column  VARCHAR (64) 
     CREATE  TABLE  #t ( 
         tablename  VARCHAR (64), 
         columnname  VARCHAR (64) 
     ) 
     DECLARE  TABLES  CURSOR  FOR 
     SELECT  o.name , c.name  FROM  syscolumns c 
     INNER  JOIN  sysobjects o  ON  c.id = o.id 
     WHERE  o.type =  'U'  AND  c.xtype  IN  (167, 175, 231, 239) 
	 AND c.name=@where_column
     ORDER  BY  o. name , c. name 
     OPEN  TABLES 
         FETCH  NEXT  FROM  TABLES 
         INTO  @table , @column 
         WHILE @@FETCH_STATUS = 0 
         BEGIN 
             SET  @sql =  'IF EXISTS(SELECT NULL FROM ['  + @table  +  '] ' 
             SET  @sql = @sql +  'WHERE RTRIM(LTRIM(['  + @column  +  '])) = '''  + @value +  ''') ' 
             SET  @sql = @sql +  'INSERT INTO #t VALUES ('''  + @table  +  ''',''' 
             SET  @sql = @sql + @column  + ''')' 
             EXEC (@sql) 
             FETCH  NEXT  FROM  TABLES 
             INTO  @table , @column 
         END 
     CLOSE  TABLES 
     DEALLOCATE  TABLES 
     SELECT  *  FROM  #t 
     DROP  TABLE  #t 
End

--创建临时表存储查询的结果
 	CREATE  TABLE  #t1 ( 
     tablename  VARCHAR (64), 
     columnname  VARCHAR (64) 
 ) 

 --目标字段
 DECLARE @WhereColumnName nvarchar(50)
 set @WhereColumnName='FnitID'
 --目标值
 DECLARE @WhereColVal nvarchar(100)
 set @WhereColVal='b23a0634'
--查询存储过程得到存在相关字段和值的表数据
 INSERT INTO #t1 EXEC dbo.SP_FindValueInDB @WhereColVal,@WhereColumnName

--（可以省略）排除你不需要更新的数据表
 select * from #t1 where not exists(
 SELECT 1 FROM #t1 as tn where tablename like '%Desk_%' AND tn.tablename=#t1.tablename
 UNION ALL
 SELECT 1 FROM #t1 as tn where tablename like '%File_%' AND tn.tablename=#t1.tablename
 )

 --目标值（ FnitID 字段值从 'b23a0634' 更新 为 '2abcd090'）
 DECLARE @TargetColVal nvarchar(100)
 set @TargetColVal='2abcd090'
 DECLARE @WhereColVal nvarchar(100)
 set @WhereColVal='b23a0634'

DECLARE @Sql NVARCHAR(MAX) 
DECLARE UpdateCursor CURSOR FOR 
select (' UPDATE '+ tablename + ' SET '+ columnname+ ' ='''+@TargetColVal+''' '+ ' WHERE 	'+columnname+'='''+@WhereColVal+''' ' ) as tableSQL from #t1
where not exists(
 	SELECT 1 FROM #t1 as tn where tablename like '%Desk_%' AND tn.tablename=#t1.tablename
 	UNION ALL
 	SELECT 1 FROM #t1 as tn where tablename like '%File_%' AND tn.tablename=#t1.tablename
 	)
OPEN UpdateCursor 

FETCH NEXT FROM UpdateCursor 
INTO @Sql 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	EXEC( @Sql)

	FETCH NEXT FROM UpdateCursor 
	into @Sql
END 


CLOSE UpdateCursor 
DEALLOCATE UpdateCursor 

DROP TABLE #t1

存储过程参考 ：https://blog.csdn.net/zengcong2013/article/details/55264202