T-SQL 2005: combine multiple create/alter procedure calls in one transaction

Question

I want to build a T-SQL change script that rolls out database changes from dev to test to production. I've split the script into three parts:

1. DDL statements
2. changes for stored procedures (create and alter procedure)
3. data creation and modification

I want all of the changes in those three scripts to be made in a transaction. Either all changes in the script are processed or - upon an error - all changes are rolled back. I managed to do this for the steps 1 and 3 by using the try/catch and begin transaction statements. My problem is now to do the same thing for the stored procedures. A call to "begin transaction" directly before a "create stored procedure" statement results in a syntax error telling me that "alter/create procedure statement must be the first statement inside a query batch". So I wonder how I could combine multiple create/alter procedure statements in one transaction.

Any help is highly appreciated ;-)

Thanks

Answer 1

You can use dynamic SQL to create your stored procedures.

EXEC ('CREATE PROC dbo.foo AS ....`)

This will avoid the error "alter/create procedure statement must be the first statement inside a query batch"