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:
When Stored Procedure break execution on error?
As an introduction – today’s question is a follow-up after last week’s Does Stored Procedure rollback on error? post.
Answer:
As in many situations this time the only correct answer is: It depends!
It depends on error severity
On Database Engine Error Severities page we can find:
Error messages with a severity level of 19 or higher stop the execution of the current batch.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.
We can check it in very easy way:
-- You need to be member of the sysadmin server role
-- Create test Stored Procedure
CREATE PROCEDURE dbo.usp_TestSeverity
AS
BEGIN
SELECT 1 AS [Result];
RAISERROR ('Let''s generate some error', 19 /*severity*/, 1 /*state*/) WITH LOG;
SELECT 2 AS [Result];
END;
GO
-- Execute test Stored Procedure
EXEC dbo.usp_TestSeverity;
-- Cleanup
DROP PROCEDURE dbo.usp_TestSeverity;
As expected, we get only one result set:
OK, so this is quite simple rule. Every error with severity 19 and higher breaks Stored Procedure execution. What about errors with lower severity?
It depends on error type
This is a bit more complicated. Take a look on below examples:
Example 1:
-- Create test Stored Procedure
CREATE PROCEDURE dbo.usp_TestLowSeverity1
AS
BEGIN
SELECT 1 AS [Result];
SELECT * FROM dbo.TableDoesntExist;
SELECT 2 AS [Result];
END;
GO
-- Execute test Stored Procedure
EXEC dbo.usp_TestLowSeverity1;
-- Cleanup
DROP PROCEDURE dbo.usp_TestLowSeverity1;
As a result we get following error with severity 16:
Msg 208, Level 16, State 1, Procedure dbo.usp_TestLowSeverity1, Line 6 Invalid object name 'dbo.TableDoesntExist'.
As you can notice this error stopped execution:
Example 2:
-- Create test Stored Procedure
CREATE PROCEDURE dbo.usp_TestLowSeverity2
AS
BEGIN
SELECT 1 AS [Result];
SELECT 100/0 AS [Result];
SELECT 2 AS [Result];
END;
GO
-- Execute test Stored Procedure
EXEC dbo.usp_TestLowSeverity2;
-- Cleanup
DROP PROCEDURE dbo.usp_TestLowSeverity2;
In this situation as a result we also get error with severity 16:
Msg 8134, Level 16, State 1, Procedure dbo.usp_TestLowSeverity2, Line 6 Divide by zero error encountered.
However this time it didn’t stop execution of Stored Procedure:
Errors with severity lower than 19 sometimes break Stored Procedure’s execution and sometimes not. In this case it more depends on error type and …
It depends on XACT_ABORT setting
From SET XACT_ABORT page:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
Are you wondering how it can affect execution of Stored Procedure? Let’s change Example 2 from above by adding SET XACT_ABORT ON:
SET XACT_ABORT ON;
GO
-- Create test Stored Procedure
CREATE PROCEDURE dbo.usp_TestLowSeverity2
AS
BEGIN
SELECT 1 AS [Result];
SELECT 100/0 AS [Result];
SELECT 2 AS [Result];
END;
GO
-- Execute test Stored Procedure
EXEC dbo.usp_TestLowSeverity2;
-- Cleanup
DROP PROCEDURE dbo.usp_TestLowSeverity2;
This time we got exactly same error:
Msg 8134, Level 16, State 1, Procedure dbo.usp_TestLowSeverity2, Line 6 Divide by zero error encountered.
However this time exception stopped Stored Procedure’s execution:
Things are getting more complicated. XACT_ABORT setting has huge impact on Stored Procedure behavior and whether error break its execution or not. But it is still not everything.
It depends on TRY … CATCH use
TRY…CATCH construct:
[…] catches all execution errors that have a severity higher than 10 that do not close the database connection.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
- Compile errors, such as syntax errors, that prevent a batch from running.
- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
These errors are returned to the level that ran the batch, stored procedure, or trigger.
With such knowledge we can add TRY…CATCH construct to ours previous example and see what happens.
CREATE PROCEDURE dbo.usp_TestTryCatch
AS
BEGIN
BEGIN TRY
SELECT 1 AS [Result];
SELECT 100/0 AS [Result];
SELECT 2 AS [Result];
END TRY
BEGIN CATCH
SELECT 3 AS [Result];
END CATCH
END;
GO
-- Execute test Stored Procedure
EXEC dbo.usp_TestTryCatch;
-- Cleanup
DROP PROCEDURE dbo.usp_TestTryCatch;
This time, no error was returned and Stored Procedure execution was not stopped. Divide by zero exception was handled properly and as a result Stored Procedure was executed successfully.
Summary
As you can see there is many factors which needs to be taken into consideration to tell whether error will break Stored Procedure execution or not. In this post I described following ones:
- Error severity
- Error type
- XACT_ABORT setting
- Usage of TRY…CATCH
If you are interested in more detailed lecture I strongly encourage you to read Erland Sommarskog (b) three-part article about Error and Transaction Handling in SQL Server. In part 2 of this series you can find this comprehensive summary:
This in simple way presents the multiplicity of options we have. I hope this clearly explains why the most correct answer on question: When Stored Procedure break execution on error? is it depends!
-Marek
Share it:
