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.
 I have changed the PRINT statements to SELECT so that the notices are presented along with the original values and altered values data.
4 thoughts on “Simple way to make ad hoc SQL scripts that can be tested”
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.