I've had the pleasure of working in multi-tiered environments for much of my time as a developer. Code is written in development, moved to a test environment, or two or three, and then migrated to production with a release. Each environment has its own application and database servers.

To facilitate migration between these environments, all SQL is put into a task-specific script file, and committed to source control. Since it is possible for a script to be executed more than once per environment, given the need for bug fixes or feature creep, the SQL needs to be written to be run-twice safe.

Run-twice safe means I can safely run the same script in the same environment multiple times without entering duplicate data or reaching an exception due to already extant schema changes.

Since I work primarily with MS SQL Server, my example below is written in T-SQL. However, the concept should work with almost any RDBMS.

The keys to run-twice safe are:

  • Try/Catch
  • Transactions
  • Conditionals
  • Error checking

Try/Catch

The entire script is wrapped in a try/catch block to allow us to catch and gracefully handle any exceptions which may arise.

view plain print about
1BEGIN TRY
2 // do stuff
3END TRY
4BEGIN CATCH
5 // handle the error
6END CATCH

Transactions

The functional portion of the script is wrapped in a transaction block. If one part of the script fails, we want to roll the entire thing back. Since the failure is an exception, the catch block will handle the rollback.

view plain print about
1BEGIN TRY
2 BEGIN TRANSACTION
3 // do stuff
4 COMMIT TRANSACTION
5END TRY
6BEGIN CATCH
7 IF @@TRANCOUNT >
0
8 ROLLBACK TRANSACTION
9 // handle the error
10END CATCH

Checking to see if a transaction is open before attempting a rollback is a safety valve. If you are worried about multiple transactions being open, the rollback could be rewritten to be

view plain print about
1WHILE @@TRANCOUNT > 0
2 ROLLBACK TRANSACTION

Conditionals

Before adding, subtracting, or updating anything, whether data or schema-related, perform a check to see if that bit of work is needed. For example, before adding a new table, check to see if it already exists.

view plain print about
1IF NOT EXISTS (
2 SELECT table_name FROM information_schema.tables WHERE table_name = 'sometable'
3BEGIN
4 // create the table
5END

For schema changes, I heavily depend on the INFORMATION_SCHEMA views. It just makes my life that much easier. For data changes, I look to see if the value I want to change has already changed, or even exists.

view plain print about
1IF NOT EXISTS (
2 SELECT somecolumn FROM sometable WHERE somecolumn = 'new value'
3)
4BEGIN
5 // insert/update the value
6END

Error Checking

Finally, if an error does happen, then we need to remember to throw it back to the console, as the try/catch block will swallow it. Using a RAISERROR statement, for T-SQL, in the catch block ensures we know what went wrong.