TSQL Autonomous Transactions or Another Reason I Like PLSQL over TSQL

Since I have both an MCDBA and OCP I often get asked which RDBMS I like better, Oracle or SQL Server?  The stock answer is, "it depends" (what a cop-out).  But for sure, PL/SQL is light years ahead of TSQL.  Some of my complaints with TSQL:

• terrible error handling.  Yes I know all about TRY/CATCH.  I also know that there are still plenty of error conditions that occur before TRY/CATCH is invoked.
• When assigning potentially multiple values to a variable, no error is thrown.  Consider this example:

      DECLARE @variable varchar(200)

If you've never seen the goofy VALUES syntax it is [[Row Constructors]] syntax.  Don't get caught up in the weeds, basically the variable will always be set to the last value returned.  For this example it will likely be Angie Wentzel every time, but can't be guaranteed and should generate an error.  Oracle does this via the error directive TOO_MANY_ROWS (or OTHERS at a minimum).  We should not be allowed to assign a non-scalar query result to a scalar variable. 

• No autonomous transactions.  You can also consider this inconsistent temporary object behavior. 

Oracle has these things called PRAGMAs which is a fancy term for a "compiler directive".  In Oracle these are often misused and misunderstood.  There's really only one good use for them...logging error messages.  Assume a long running, multi-step batch process with a single transaction where I wish to log status messages as I go along.  Now assume near the end of the process it fails and I need to ROLLBACK.  I can of course catch the error and write out informational messages, but I cannot log anything to a table, especially those intermediate steps, since the transaction that rolls back will also ROLLBACK the logging.  Darn.  In Oracle we can write our logging routine using PRAGMA AUTONOMOUS_TRANSACTION which essentially means that routine does not participate in any existing transaction.  Problem solved.

For the longest time we didn't have this...until the introduction of table variables.  This "feature" is not well documented.  You can in fact do this:

      DECLARE @variable varchar(200)

Well, that's pretty cool and solves our logging problem, but if you didn't fully understand that behavior and continued to reuse a table variable after a ROLLBACK you will get unexpected results.

it also violates ACID properties of a transaction.  Some think a table variable isn't really a part of the db, it is a temporary object, but then why is [[temporary objects|the behavior different for temp tables in tempdb]]?  TSQL needs more consistency here.