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. Here is the error:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'xyz'.
But this time there is a result:
Why? What is going on?