QuickQuestion: Does Stored Procedure rollback on error?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends and co-workers, mainly application developers.

Today’s question:

Does Stored Procedure rollback on error?

Answer:

Stored Procedures can contain many different T-SQL statements and these statements aren’t by default covered by any implicit transaction. Each of these single statements is of course atomic. That simply means it will be completed in 100% or won’t be done at all. However this is not the way how Stored Procedures works. In case of error some of the statements in SP may be completed and will not be rolled back, and some of them may not be executed.

Example:

-- Create test objects
CREATE TABLE dbo.Test (
    [Id]    int IDENTITY,
    [Value] int NOT NULL
);
GO

CREATE PROCEDURE dbo.usp_InsertData
AS
BEGIN
    INSERT INTO dbo.Test ([Value])
    VALUES (10/2);
    
    INSERT INTO dbo.Test ([Value])
    VALUES (10/0); -- error
    
END;
GO

-- Execute test Stored Procedure
EXEC dbo.usp_InsertData;

-- Check data in table
SELECT Id, Value FROM dbo.Test;

-- Cleanup
DROP PROCEDURE dbo.usp_InsertData;
DROP TABLE dbo.Test;

During Stored Procedure execution we get expected error:

Msg 8134, Level 16, State 1, Procedure usp_InsertData, Line 8 [Batch Start Line 21]
Divide by zero error encountered.
The statement has been terminated.

Let’s take a look on data in the table:

As you can see the table contains result of first T-SQL statement from Stored Procedure. Even when second statement caused exception this doesn’t rollback result of the first one. It is very important to remember about that behavior, because in result you may end up with inconsistent data.

If you want to make your Stored Procedure atomic, you should create explicit transaction and use TRY…CATCH construct.

-- Create test objects
CREATE TABLE dbo.Test (
    [Id]    int IDENTITY,
    [Value] int NOT NULL
);
GO

CREATE PROCEDURE dbo.usp_InsertData
AS
BEGIN
    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.Test ([Value])
        VALUES (10/2);
    
        INSERT INTO dbo.Test ([Value])
        VALUES (10/0); -- error
    
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
    END CATCH
END;
GO

-- Execute test Stored Procedure
EXEC dbo.usp_InsertData;

-- Check data in table
SELECT Id, Value FROM dbo.Test;

-- Cleanup
DROP PROCEDURE dbo.usp_InsertData;
DROP TABLE dbo.Test;

-Marek

Share it: