Updated Testable SQL Script Template

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.