list of batch aborting errors in SQL server

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.

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.

StackExchange Database Administrators Q#126467, answer score: 7