10

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) and some errors will abort the whole batch (for example supplying parameters to a stored procedure that doesn't take parameters). [Reference]: http://www.sommarskog.se/error-handling-I.html#scope-abortion.

What I would like to know is whether there is a definitive list of which errors are batch aborting and which ones are statement terminating.

7

I believe there are a few exceptions, but from Database Engine Error Severities (MSDN):

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.

So it seems like you could get a definitive list from the following query (of course this will not allow you to filter out which ones can be caused by user T-SQL):

SELECT message_id, severity, [text]
FROM sys.messages
WHERE language_id = 1033 
AND severity >= 19
ORDER BY severity, message_id;

In SQL Server 2012, this produces 210 rows.

In SQL Server 2016, this produces 256 rows.

By the way, I don't believe the two scenarios you describe in your question work the way you think, at least not in modern versions of SQL Server. I tried this on both 2012 and 2016 (I believe Erland's article describes SQL Server 2000 behavior, which I don't remember if it was any different, but not very relevant today even if so).

USE tempdb;
GO

CREATE PROCEDURE dbo.pA -- no parameters
AS PRINT 1
GO
CREATE PROCEDURE dbo.pB -- two parameters
@x INT, @y INT
AS PRINT 1
GO

SET XACT_ABORT OFF;
GO

EXEC dbo.pA @foo = 1; 
PRINT '### Calling procedure that doesn''t take parameters with a parameter';
GO

EXEC dbo.pB; 
PRINT '### Calling procedure that takes 2 parameters with no parameters';
GO

EXEC dbo.pB @x = 1; 
PRINT '### Calling procedure that takes 2 parameters with not enough parameters';
GO

EXEC dbo.pB @x = 1, @y = 2, @z = 3; 
PRINT '### Calling procedure that takes 2 parameters with too many parameters';
GO

These all produce errors of severity level 16, and all of them proceed with the batch, as evidenced by the print output:

Msg 8146, Level 16, State 2, Procedure pA, Line 11
Procedure pA has no parameters and arguments were supplied.
### Calling procedure that doesn't take parameters with a parameter
Msg 201, Level 16, State 4, Procedure pB, Line 14
Procedure or function 'pB' expects parameter '@x', which was not supplied.
### Calling procedure that takes 2 parameters with no parameters
Msg 201, Level 16, State 4, Procedure pB, Line 18
Procedure or function 'pB' expects parameter '@y', which was not supplied.
### Calling procedure that takes 2 parameters with not enough parameters
Msg 8144, Level 16, State 2, Procedure pB, Line 22
Procedure or function pB has too many arguments specified.
### Calling procedure that takes 2 parameters with too many parameters

As I suspected, there are exceptions, of course, as noted in the comments. Conversion failure is severity 16 but aborts the batch:

SET XACT_ABORT OFF;
SELECT CONVERT (INT, 'foo');
PRINT 'Made it.'; -- no print happens

Results do not include the print output this time:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'foo' to data type int.

3
  • Thank you very much! I thought I wouldn't be able to use the severity level as an indicator because of the prior inconsistency. Very glad to hear that isn't the case. Jan 18 '16 at 4:23
  • Aargh! There are still some severity level 16 errors that will abort the batch. If I select and execute: begin tran print @@TRANCOUNT print convert(int, 'abc') followed by: print @@TRANCOUNT There will be a level 16 error but the batch will be aborted. Jan 18 '16 at 6:29
  • BTW, if you discover cases where the exception is raised with a different severity from the declared one, please report them through connect Jan 18 '16 at 7:31
3

In addition to the types of errors noted by @Aaron (i.e. Severity >= 19 and conversion failures), the following types of errors, noted in the MSDN page for TRY...CATCH, will also abort a batch:

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.

In the examples below, please note that three of them are even Severity Level 15.

EXAMPLE 1

SET XACT_ABORT OFF;
SELECT @NotDeclared; -- parse error
PRINT 'Do you see me?';

Returns:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@NotDeclared".

EXAMPLE 2

SET XACT_ABORT OFF;
InvalidSQL; -- parse error
PRINT 'Do you see me?';

Returns:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'InvalidSQL'.

EXAMPLE 3

SET XACT_ABORT OFF;
SELECT 1 -- statement preceding THROW not terminated by semicolon
THROW 50505, N'Error, yo', 1; -- parse error
PRINT 'Do you see me?';

Returns:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '50505'.

EXAMPLE 4

SET XACT_ABORT OFF;
SELECT NoSuchColumn FROM sys.objects; -- compilation error
PRINT 'Do you see me?';

Returns:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'NoSuchColumn'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.