SQL Server BEGIN TRAN – COMMIT Strange Behavior Explained

sql serversql-server-2012

First create two tables:

CREATE TABLE xyz (Id INT)
CREATE TABLE abc (Id INT)

Please, observe the following SQL code:

DELETE FROM abc
BEGIN TRAN
EXEC('
INSERT INTO xyz VALUES (1),(2)
DECLARE @x INT = (SELECT Id FROM xyz)
')
INSERT INTO abc VALUES (1)
COMMIT

SELECT * FROM abc

Running it outputs the following error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

And there is no result. All as expected.

Now let us change the error to a syntax error by replacing xyz) with xyz. Here is the error:

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

But this time there is a result:

enter image description here

Why? What is going on?

Best Answer

You can control the behaviour of your first example by specifying:

SET XACT_ABORT ON

At the top of your script, then you don't get a result. You do get a result as you describe if you specify SET XACT_ABORT OFF.

The reason for the different behaviour is the second example "replacing xyz) with xyz" contains a syntax error i.e. the EXEC batch is parsed, syntax error discovered and the batch is not run. The EXEC is a separate batch (this is the reason you cannot define variables outside of EXEC and reference them inside) and processing continues with the rest of the transaction.

For the first example "(SELECT Id FROM xyz)", this is valid syntax, so the batch parses correctly. The error is only found at run time and processing continues if SET XACT_ABORT OFF but stops if SET XACT_ABORT ON.

Related Question