Sql Server: A seemingly strange behaviour of BEGIN TRAN - COMMIT

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.

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.