DDL and DML in Single Script

Question

Hi All,

Running SQL Server 2008 R2.  We currently apply schema and data changes to databases (for software upgrades) using separate scripts.  Sometimes, based on the changes required this results in several scripts for a single upgrade.  I'd like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues.  Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).

I'm aware of the fact that we can strip out these batch separators, but what I'm not clear on is what, if any, negative impact that would have.  Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?

Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first).  If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN...END or something similar so the column existence check would only have to be performed once for that group of changes?

Thanks in advance!

Brad

Answer 1

HI,

Use named transaction with try catch blocks

BEGIN TRAN sTranName

GO

try

catch

ROLLBACK TRAN sTranName

GO

try

catch

ROLLBACK TRAN sTranName

GO

try

catch

ROLLBACK TRAN sTranName

Answer 2

You can remove GO or batch separator if you don't have:

1. same variables declared in more than 1 batch

2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE' must be the first statement in a query batch.)

3. there could be more things, just check based upon my above 2 points.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page