Back in May, I posted a template for SQL scripts. That template allowed the script to be run repeatedly in Report mode – displaying changes without actually applying those changes to the database. And then, when ready, and with a simple change of one variable, the same script would run and apply all changes to the data.
That template has proved very useful to me. However, I occasionally run into the situation where the changes that are done to rows invalidate the WHERE clause used to find the problem records. For example:
... SELECT 'original values', * FROM foo WHERE bar IS NULL; UPDATE foo SET bar = 'snafu' WHERE bar IS NULL; SELECT 'new values', * FROM foo WHERE bar is NULL;
The second SELECT will not display the altered rows.
To help with that situation, I have updated the template as follows:
DECLARE @mode VARCHAR(1); SET @mode = 'R' -- set to 'R' for Report or 'U' for update BEGIN TRANSACTION SELECT 'orginal values of affected stuff' ,***affected stuff*** FROM ***source*** WHERE ***selection clause*** DECLARE @tempTable TABLE ( ***keyval*** ***type*** ,***additionalInfo*** ***type*** ); INSERT/UPDATE/DELETE ***source*** ***affected stuff*** OUTPUT INSERTED|DELETED.***keyval*** INTO @tempTable WHERE ***selection clause*** SELECT 'updated values of affected stuff' ,***affected stuff*** FROM ***source*** s JOIN @tempTable tt ON tt.***keyval*** = s.***keyval*** IF @mode = 'U' BEGIN COMMIT TRANSACTION SELECT 'Changes have been applied to the ' + DB_NAME() + ' database' [notice] END ELSE BEGIN ROLLBACK TRANSACTION SELECT 'All changes have been rolled back from ' + DB_NAME() [notice] END
Hopefully that makes things a little easier in the future.