What is the scope of TRANSACTION in Sql server
Posted
by Shantanu Gupta
on Stack Overflow
See other posts from Stack Overflow
or by Shantanu Gupta
Published on 2010-03-15T13:16:17Z
Indexed on
2010/03/15
13:19 UTC
Read the original article
Hit count: 300
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 ?
© Stack Overflow or respective owner