Simple way to make ad hoc SQL scripts that can be tested


This is another one of those entries that might not seem like a big deal. But when you are in the middle of a 32-hour shift, your brain needs more than caffeine to remember best practices (as I recently found out first-hand). Sometimes a simple entry like this can be a life-saver.

DECLARE @mode VARCHAR(1);
SET @mode = 'R' -- set to 'R' for Report or 'U' for update

BEGIN TRANSACTION
    SELECT 'original values'
          ,affected_stuff
      FROM source_tables
     WHERE condition;

    UPDATE source_tables
       SET affected_stuff = new_values
     WHERE condition;

    SELECT 'altered values'
          ,affected_stuff
      FROM source_tables
     WHERE condition;

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

The @mode parameter provides an easy way run to the code and ensure that the results are as expected, all without actually affecting the DB (all changes to the DB are rolled back at the end). Once the code looks good, just change the @mode to ‘U’ and execute it again.
Selecting the affected data before and after alteration, of course, provides a simple way to validate that the changes are as expected.

[edit] I have changed the PRINT statements to SELECT so that the notices are  presented along with the original values and altered values data.

Advertisements

2 thoughts on “Simple way to make ad hoc SQL scripts that can be tested”

  1. Jeez Clive, why were you working a 32 hour shift?

    Great post by the way. I think I’ll keep that in mind for my Stored Procedures in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s