What is the scope of TRANSACTION in Sql server
- by Shantanu Gupta
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
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
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. But when i tried to run both the code, both was working in the same way.
I am confused to know how does TRANSACTION works. Is it scope free or what ?
