Tuesday, February 1, 2011

Fun with TRANSACTION

What will be output of below T-SQL code:

CREATE TABLE MyTable
(
   MyId [INT] IDENTITY (1,1),
   MyCity [NVARCHAR](50)
)


BEGIN TRANSACTION OuterTran
  INSERT INTO MyTable VALUES ('Boston')
  BEGIN TRANSACTION InnerTran
    INSERT INTO MyTable VALUES ('London')
    ROLLBACK WORK
    IF (@@TRANCOUNT = 0)
    BEGIN
      PRINT 'All transactions were rolled back'
    END
    ELSE
    BEGIN
      PRINT 'Outer transaction is rolling back...'
      ROLLBACK WORK
    END
    DROP TABLE MyTable

Here are the options:
1.   All transactions were rolled back
2.   Outer transaction is rolling back...
3.   ERROR: Incorrect syntax near 'WORK'.