T-SQL install / upgrade script as a transaction

Question

I'm trying to write a single T-SQL script which will upgrade a system which is currently in deployment. The script will contain a mixture of:

New tables
New columns on existing tables
New functions
New stored procedures
Changes to stored procedures
New views
etc.

As it's a reasonably large upgrade I want the script to rollback if a single part of it fails. I have an outline of my attempted code below:

DECLARE @upgrade NVARCHAR(32);
SELECT @upgrade = 'my upgrade';

BEGIN TRANSACTION @upgrade
BEGIN
    PRINT 'Starting';
    BEGIN TRY
        CREATE TABLE x ( --blah...
        );

        ALTER TABLE y --blah...
        );

        CREATE PROCEDURE z AS BEGIN ( --blah...
        END
        GO  --> this is causing trouble!

        CREATE FUNCTION a (  --blah...

    END TRY
    BEGIN CATCH    
        PRINT 'Error with transaction. Code: ' + @@ERROR + '; Message: ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION @upgrade;
        PRINT 'Rollback complete';
        RETURN;
    END TRY
END
PRINT 'Upgrade successful';
COMMIT TRANSACTION @upgrade;
GO

Note - I know some of the syntax is not perfect - I'm having to re-key the code

It seems as though I can't put Stored Procedures into a transaction block. Is there a reason for this? Is it because of the use of the word GO? If so, how can I put SPs into a transaction block? What are the limitations as to what can go into a transaction block? Or, what would be a better alternative to what I'm trying to achieve?

Thanks

you should be able to wrap all of your DDL statements in one large transaction, although as mentioned you'll have to remove any `GO` statements. I would also advise backing up your database before you start - that way you'll have the option of the uber rollback statement, `RESTORE DATABASE`. — paul, Feb 22 '13 at 16:08

score 3 · Answer 1 · answered Feb 22 '13 at 20:33

As Thomas Haratyk said in his answer, your issue was the "go". However, you can have as many batches in a transaction as you want. It's the try/catch that doesn't like this. Here's a simple proof-of-concept:

begin tran
go
select 1
go
select 2
go
rollback

begin try
    select 1
    go
    select 2
    go
end try
begin catch
    select 1
end catch

score 2 · Accepted Answer · edited May 23 '17 at 11:54

2

Remove the GO and create your procedure by using dynamic sql or it will fail.

EXEC ('create procedure z 
  as
  begin
    print "hello world"
  end')

GO is not a SQL keyword, it is a batch separator. So it cannot be included into a transaction.

Please refer to those topics for further information :

sql error:'CREATE/ALTER PROCEDURE' must be the first statement in a query batch?

Using "GO" within a transaction

http://msdn.microsoft.com/en-us/library/ms188037.aspx

edited May 23 '17 at 11:54

Community

1
1

answered Feb 22 '13 at 15:27

jazzytomato

6,994
2
31
44

I have tried removed the `GO`, however I then get syntax errors on the next `BEGIN` block, which result in a huge number of other syntax errors chained along to the end of the script! – Arj Feb 22 '13 at 15:31
See my edit, create a procedure needs to be the first statement if not embedded in dynamic sql – jazzytomato Feb 22 '13 at 15:33
Will this allow multiple `EXEC` statments? – Arj Feb 22 '13 at 16:11
Yes you can use multiple EXEC into your transaction – jazzytomato Feb 22 '13 at 16:22

T-SQL install / upgrade script as a transaction

2 Answers2