Hi Friends,
Here i am using the cursor in my store procedure, i am planning to take out my cursor in my SP , please give some solution how to avoid the cursor statement to normal update statement with dynamic .
Example Below:
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)
3 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)
This is how my cursor will work 300 Combination dynamically pick the data from table and update into the main table
I am trying to take out the cursor , and update statement should work similar like this , instead of writing 300 update statement i want to write one update where all the 300 combination should execute.
below the code i have to bring another solution.
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