CODEWITHSUNDEEP
sqlsql-serversql-server-2012transactions
Alexander
Alexander

Reputation: 4527

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?

Upvotes: 0

Views: 2746

Answers (1)

anshev0
anshev0

Reputation: 136

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

Upvotes: 2

Related Questions