QuickQuestion: When Stored Procedure break execution 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:

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:

high severity

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:

low severity - execution stopped

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:

low severity - execution not stopped

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:

low severity - xact_abort on

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:

  1. Error severity
  2. Error type
  3. XACT_ABORT setting
  4. 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:

Erland Sommarskog summary
http://www.sommarskog.se/error_handling/Part2.html#classification

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:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.