Select into is selecting last record when trying to generate dynamic update statement for each table

Question

I have a table like below :

Archival:

TableName        ColumnName         Datatype           NewValue
Employee         FirstName          nvarchar           Abc
Employee         LastName           nvarchar           Pqr
Employee         Age                int                28
Address          City               nvarchar           Chicago
Address          StreetName         nvarchar           KKKKK

I am trying to create dynamic update statement for each table and want to execute update statement for each individual table and rollback if any update fails.

Expected update statement query for each individual table:

Update Employee set FirstName = 'Abc', LastName = 'Pqr', Age = 21 
where DepartmentId = 100;

Update Address set City = 'Chicago', StreetName = 'KKKKK'
where DepartmentId = 100;

Stored Procedure:

Alter PROC [dbo].[UpdateDataByDeptID]
  @departmentId int
As
 Begin
       Declare db_cursor CURSOR for select TableName from Archival 
       group by TableName
       
       Declare @tableName nvarchar(50),
               @columnName nvarchar(50),
               @datatype nvarchar(50),
               @newValue nvarchar(50)

        open db_cursor
        Fetch Next from db_cursor into @tableName;
        While @@FETCH_STATUS = 0
        Begin
               select @columnName = ColumnName,
                      @datatype = Datatype,
                      @newValue = NewValue
               from Archival where TableName = @tableName;
               
               print @tableName + ' ' + @columnName + ' ' + @newValue
            Fetch Next from db_cursor into @tableName
        End;
        close db_cursor;
        DEALLOCATE db_cursor;
        
    Begin Transaction
        Begin Try
            --execute each of the update statement like below for ex:
            Update Employee .....
            Update Address .....
          Commit transaction
        End Try
        Begin Catch
          Rollback
        End Catch
    End
End -- end of SP

But the problem is this line :

print @tableName + ' ' + @columnName + ' ' + @newValue

Above line is giving me last record for each table. For example for Employee table :

Employee         Age                int                28

Hence I am not able to generate individual update statement for each table.

Final expected output is below for my transaction after each update statement for each individual table:

Begin Transaction
        Begin Try
            --execute each of the update statement like below for ex:
              Update Employee set FirstName = 'Abc', LastName = 'Pqr', Age = 21 
              where DepartmentId = 100;

              Update Address set City = 'Chicago', StreetName = 'KKKKK'
              where DepartmentId = 100;
          Commit transaction
        End Try
        Begin Catch
          Rollback
        End Catch
End

Can someone please help me with this?

Answer 1

An example of how you could approach something like this is by using dynamic SQL.

For example:

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

DECLARE @tablename NVARCHAR(255);

DECLARE db_cursor CURSOR FOR
    SELECT DISTINCT [TableName] FROM [Archival];

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql += N'UPDATE ' + QUOTENAME(@tablename) + ' SET ';
    SELECT @sql += QUOTENAME([ColumnName]) + N' = ' + QUOTENAME([NewValue], '''') + N', '
    FROM [Archival]
    WHERE [TableName] = @tablename;
    SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + N'
WHERE DepartmentId = 100;
';
    FETCH NEXT FROM db_cursor INTO @tablename;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

PRINT @sql;
-- EXEC sp_executesql @sql;

Which should give you something to your expected output, I believe.

I would be careful when doing something like this though if you can't trust what's in the NewValue column. (e.g. if it's possible for someone to put in some sort of malicious SQL that would be executed here).