Procedural, Transact SQL — Batches

When building solutions in T-SQL, you’ll often be asked to generate a change script.  This change script is a batch of SQL statements that when executed sequentially (and completely) will make a change to the database for some business reason.

When you build these, you can use every tool you’ve learned about SQL, but there are some things you’ll need to keep in mind.

SQL Server will compile all the statements you send in that batch into a single executable unit, called an execution plan. The statements in this execution plan are then executed once.

Any compile error, syntax error, etc, will prevent the  compilation of the execution plan. As a result: no statements from your batch  will be executed.

But a run-time error will cause additional problems. Most run-time errors will stop at the current statement causing the error and the statements following it won’t be run.  If you can wrap your whole code in a transaction, you could then use a try catch, or a manual method, to ROLLBACK your transaction should a run-time error occur.

Constraint violation errors, only stop the current statement. All the  remaining statements in the batch should still execute.

If you’re releasing DDL changes via a script, use a new .sql file for each object you’re releasing.  First, it makes source control much easier.  Second, you can’t chain together multiple DDL statements, at least, not without separating them with GO statements.  But then, if you are sharing a variable across these releases, you’re going to run outside the scope of those variables.  This could get dicey!

In the future, you’re going to have to learn to make complex change scripts that can alter a table and manage the data within that table in a production environment.  These can get very interesting!  Remember, when you make a change that you’re not 100% sure you can pull off, test it in your local development environment first.

Test it multiple times.

Ask someone you trust for a sanity check on your code.  Better safe than sorry!

That’s it for this time.  Let me know if you have any questions!

Leave a Reply

Your email address will not be published. Required fields are marked *