Build a dynamic list of UPDATE statement values in SQL server

Question

As part of "rollback" solution, For each update, I would like to create separate Update statement to undo the values.
So we could execute this update statement for Rollback purpose.

I am looking for generic update statement.

Requirement is below,

I have to pass below input parameters

schema name,
table name,
list of columns to be updated,
condition column,
value for condition column

When i execute the query, then update statement should display in result set so that I can commit this statement in Rollback folder

Trying to achieve this using dynamic query. Please help on this.

Answer 1

No, that is not a viable strategy. It's way too advanced. For it to work, you need to test it carefully. Which means that you need to create scenarios where you need to perform that rollback. And what if the rollback fails, because of something you did not foresee? What do you do in that case? Roll back the rollback?

I don't know what sort of operation you are planning, but the best is to make sure that you have a backup of the database, and if the operation fails, you restore the backup. True, if this is a multi-terabyte database, this can take a very long time. One alternative is to create a database snapshot, and in case of an error, and you can revert from the snapshot, which is a quick operation. There is a risk, though. If your operation makes a lot of updates, the snapshot may break down due to fragmentation in the file system, in which case you will need to do the restore.

Or simply perform the operation inside a transaction, and if it fails, let SQL Server handle the rollback.

I can see scenarios where none of these alternatives are acceptable, but since I don't what your operation that you want to be able to roll back is, I stop at this point, but I reinforce what I said initially: that strategy is not going to work out.

Answer 2

Hi @Bala Krishnan

After reading your description above, we are still confused about what you plan to do.

To create this solution, you need to design, write, and test three steps. The design depends on your requirements and the size of your database. Writing comes from design, complementing and perfecting code. As ErlandSommarskog says, there will be a lot of risk in testing. This is not what we want to see.

I'm sorry, but I'm afraid your idea will be difficult to implement.

Best regards,
Percy Tang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.