7

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?

  • 5
    Can you explain why you've re-introduced this question? – Aaron Bertrand Nov 2 '16 at 13:48
  • I am not sure what is the right forum - SO or DBA. Judging by the number of views posting it on SO was probably a mistake in the first place. – mark Nov 2 '16 at 21:03
6

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.

  • Is there a way to handle all the errors the same without resorting to BEGIN TRY-END CATCH, using which results in a more verbose code - one has to rollback manually (Internet says I need to check @@TRANCOUNT before doing it) and rethrow the original error using THROW. Much code. – mark Nov 2 '16 at 21:11
9

The behaviour depends on SET XACT_ABORT and is explained in the following quote from the linked article.

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.

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.