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

Question

Hi all,

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

I don't think you are able to do this.

I don't understand why you don't just run the 3 scripts manually and if one fails you can restore to the backup you made before running the scripts. You are doing a backup before hand, right?

Answer 2

Try this:

begin transaction
    go
    create procedure foo as begin select 1 end
    go
commit transaction

Answer 3

BEGIN TRANSACTION
BEGIN TRY 
    -- Do your stuff here 
    COMMIT TRANSACTION

    PRINT 'Successfull.'

END TRY
BEGIN CATCH

    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    ROLLBACK TRANSACTION

END CATCH

Answer 4

try putting the steps in a job

Answer 5

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"