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:
Thanks. It is good to know that you need a ROLLBACK inside the CATCH block.