sql - 如何避免出现游标以及其他方法？

Question

我在存储过程中使用了游标；我希望从我的SP中删除光标。请帮助我提出一个解决方案，以解决如何避免将游标语句更改为带有动态的普通更新语句。

下面的例子：


Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'Test'(variable) and age = 18(variable)
Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'kumar'(variable) and age = 19(variable)
Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'babu'(variable) and age = 30(variable)


这就是我的光标的工作方式。 300组合从表中动态选择数据并更新到主表中

我正在尝试取出游标，并且update语句应与此类似，而不是编写300个update语句，而是要编写一个所有300个组合都应执行的更新。

下面是我的代码，需要此解决方案：

BEGIN
  DECLARE @Type VARCHAR(100)           
  DECLARE @TargetColumn VARCHAR(100)           
  DECLARE @SourceColumn VARCHAR(100)           
  DECLARE @SQL varchar(max)        

  DECLARE a_cursor CURSOR STATIC           
  FOR          
  SELECT [Type],[SourceColumn],[TargetColumn] FROM  ref.tblEdsMap        
  GROUP BY [Type],[SourceColumn],[TargetColumn]         
  OPEN a_cursor          
  FETCH NEXT FROM a_cursor INTO @Type,@SourceColumn,@TargetColumn      
  WHILE @@FETCH_STATUS = 0          
  BEGIN          


  SET @SQL = 'UPDATE  GCT SET GCT.' + @TargetColumn + ' = map.[TargetValue]       
   from  EdsMap map      
   JOIN Table GCT      
   ON GCT.' + @SourceColumn + ' = map.[SourceValue]       
   where map.[Type]=''' + @Type + ''' and map.SourceColumn=''' + @SourceColumn+ ''''       
  Exec (@SQL)        
  PRINT @SQL        
  FETCH NEXT FROM a_cursor INTO @Type,@SourceColumn,@TargetColumn      
  END          
  CLOSE a_cursor          
  DEALLOCATE a_cursor       
END

Answer 1

与使用显式游标或巧妙地伪装为while循环的游标相比，我更喜欢针对此类问题的行串联操作。

DECLARE @cmd NVARCHAR(MAX) = N'';

SELECT @cmd += N'
   UPDATE GCT 
     SET GCT.' + QUOTENAME(TargetColumn) + ' = map.TargetValue     
     FROM dbo.EdsMap AS map 
     INNER JOIN dbo.Table AS GCT      
     ON GCT.' + QUOTENAME(SourceColumn) + ' = map.SourceValue    
   WHERE map.[Type] = ''' + [Type] + ''' 
   AND map.SourceColumn = ''' + [SourceColumn]+ ''';'
FROM ref.tblEdsMap
GROUP BY [Type], SourceColumn, TargetColumn;

EXEC sp_executesql @sql;