Multiple batches in a SQL transaction

I want to create several stored procedures (or user-defined functions) within a transaction. CREATE PROCEDURE statement must be the only statement in the batch, so I have to use following template:

BEGIN TRANSACTION MyTransaction

    USE MyDatabase
    GO

    CREATE PROCEDURE A
    AS
        -- The body of procedure A
    GO

    CREATE PROCEDURE B
    AS
        -- The body of procedure B
    GO

    CREATE PROCEDURE C
    AS
        -- The body of procedure C
    GO

COMMIT TRANSACTION MyTransaction

The problem appears if an error occurs within one of the batches. For example, if error occurs while procedure B is creating, MyTransaction with procedures A and B will be rolled back. But the script will continue to run. So, the procedure C will be created.

Also it’s not possible to skip the statement using GOTO and @@error counter checking, because it cannot go to a label outside the batch in which GOTO is placed.

How to prevent any database changes if an error occurs?

Answer

You can try to do something like this with SET XACT_ABORT ON.

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [Proc A]'
GO
-------------------------------------------------------------------------
CREATE PROCEDURE A
    AS
        -- The body of procedure A
    GO
-------------------------------------------------------------------------

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [Proc B]'
GO
-------------------------------------------------------------------------
CREATE PROCEDURE B
    AS
        -- The body of procedure B
    GO
-------------------------------------------------------------------------
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO