What is the scope of TRANSACTION in Sql server

Question

I was creating a stored procedure and i got stuck in the writing methodology of me and my collegue.

I am using SQL Server 2005

1. I was writing Stored procedure like this

   BEGIN TRAN BEGIN TRY INSERT INTO Tags.tblTopic (Topic, TopicCode, Description) VALUES(@Topic, @TopicCode, @Description)

           INSERT INTO Tags.tblSubjectTopic
               (SubjectId, TopicId)
               VALUES(@SubjectId, @@IDENTITY)
         COMMIT TRAN
   END TRY
   
   
   BEGIN CATCH
           DECLARE @Error VARCHAR(1000)
           SET @Error= 'ERROR NO : '+ERROR_NUMBER() + ', LINE NO : '+ ERROR_LINE() + ', ERROR MESSAGE : '+ERROR_MESSAGE()
           PRINT @Error
           ROLLBACK TRAN
   END CATCH
   

2. And my collegue was writing it like the below one

   BEGIN TRY BEGIN TRAN INSERT INTO Tags.tblTopic (Topic, TopicCode, Description) VALUES(@Topic, @TopicCode, @Description)

           INSERT INTO Tags.tblSubjectTopic
               (SubjectId, TopicId)
               VALUES(@SubjectId, @@IDENTITY)
      COMMIT TRAN
   END TRY
   
   
   BEGIN CATCH
           DECLARE @Error VARCHAR(1000)
           SET @Error= 'ERROR NO : '+ERROR_NUMBER() + ', LINE NO : '+ ERROR_LINE() + ', ERROR MESSAGE : '+ERROR_MESSAGE()
           PRINT @Error
           ROLLBACK TRAN
   END CATCH
   

Here the only difference that you will find is the position of writing Begin TRAN. According to me the methodology of my collegue should not work when an exception occurs i.e. Rollback should not get executed because TRAN does'nt have scope in method 2. But when i tried to run both the code, both was working in the same way.

In Method 1, scope of TRAN is outside of try block so it should be visible in both try block and catch block and should give result as per the scope methodology of programming works.

In Method 2, score of TRAN is limited within Try block so Commit and Rollback should occur within that try block and should throw exception when a rollback is found in catch block, such that no begin Tran was found, but this is also working perfectly. I am confused to know how does TRANSACTION works. Is it scope free or what ?

Answer 1

Transactions are not "scoped" in the way that programming languages are.

Transactions are nested for the current connection. Each BEGIN TRAN starts a new transaction and this transaction ends whenever a COMMIT or ROLLBACK is called, it does not matter where in your stored proc this is.

Answer 2

Transactions are nested for the current connection. Each BEGIN TRAN starts a new transaction and this transaction ends whenever a COMMIT or ROLLBACK is called, it does not matter where in your stored proc this is.

only to add that ROLLBACK ends "all" open transactions for the connection...