I had been developing a saved procedure and that i got stuck within the writing methodology of me and my collegue.

I'm using SQL Server 2005

  1. I had been writing Saved procedure such as this

    BEGIN TRAN BEGIN TRY Place INTO Tags.tblTopic (Subject, TopicCode, Description) VALUES(@Subject, @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 such as the below one

    BEGIN TRY BEGIN TRAN Place INTO Tags.tblTopic (Subject, TopicCode, Description) VALUES(@Subject, @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 real difference that might be may be the position of writing Begin TRAN. Based on me the methodology of my collegue shouldn't work when the best happens i.e. Rollback shouldn't get performed because TRAN does'nt have scope in method 2. However when i attempted to operate both code, both was working in the same manner.

In Method 1, scope of TRAN is outdoors of try block so it ought to be visible both in try block and catch block and really should give result according to the scope methodology of programming works.

In Method 2, score of TRAN is restricted within Try block so Commit and Rollback should take place within that try block and really should throw exception whenever a rollback can be found in catch block, so that no begin Tran was discovered, but this is working perfectly. I'm confused to understand so how exactly does TRANSACTION works. Could it be scope free or what ?

Transactions aren't "scoped" in the manner that programming languages are.

Transactions are nested for that current connection. Each BEGIN TRAN begins a brand new transaction which transaction finishes each time a COMMIT or ROLLBACK is known as, it doesn't matter whereby your saved proc this really is.

Transactions are nested for that current connection. Each BEGIN TRAN begins a brand new transaction which transaction finishes each time a COMMIT or ROLLBACK is known as, it doesn't matter whereby your saved proc this really is.

simply to include that ROLLBACK finishes "all" open transactions for that connection...