SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Few days ago, one of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them.

Example:
PRINT 'After ROLLBACK example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
ROLLBACK TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO
PRINT '--------------------'
PRINT 'After COMMIT example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
COMMIT TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO

ResultSet:
After ROLLBACK example
1
2
2
——————–
After COMMIT example
1
2
2

It is very clear that local variables are not affected with transaction. In both of the above cases we got the same result. Jr. DBA and Application Tech Lead were pleased with this simple example and asked me to blog about it. Let me know if you like this kind of articles. I will post some more like this.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Stored Procedure to Know Database Access Permission to Current User
Next Post
SQLAuthority News – Book Review – Programming SQL Server 2005 [ILLUSTRATED]

Related Posts

19 Comments. Leave new

  • Yes, I like this kind of simple logic related articles. Please post them.
    -Jay

    Reply
  • Yes, really ur concept is very nice.

    Reply
  • thanks

    Reply
  • Hi Dave,

    I am looking for solution for the following requirement

    I am having following rows gettting from two diffrent table

    ProjectName Component Name Price
    A C1 100
    A C2 50
    A C3 50
    B C1 100
    B C3 50

    Now I want rown in followng way

    ProjectName Component Name Price
    A C1,C2,C3 200
    B C1,C3 150

    what should be the way to get such type of result

    Thanks & Regards

    Manish Bhushan

    Reply
  • pls post some more like this .Although it is a simple example but there is logic inside it .

    Reply
  • Hi pinal,

    I like this type of examples lot from your’s

    Thanks and Regards
    sreeram

    Reply
  • Nice examples,I have another one in

    that demonstrates this issue.

    Thanks
    Dan

    Reply
  • Solutions\answers which are present is your site are just supurb…as simple as they can be!!!!!!!

    Thanks a bunch for such help.

    Many Thanks,
    Chhavi

    Reply
  • Simple and Powerful

    Reply
  • Suvendu Shekhar Giri
    September 28, 2011 3:36 pm

    The article is simple and great but..
    I have to check some more scenario rather than this simple statements
    like what if there is a table variable used and after values are inserted to the table variable and then if a local variable is set to the values from that table variable….
    Although conceptually i am expecting the same result to come but let me try first

    Reply
  • Kutti Krishnan
    March 23, 2012 8:50 am

    Hi Pinal,I liked this example very much .

    Reply
  • Ajit Dubey (@insearch_ajit)
    May 3, 2012 2:13 pm

    Why I am unable to rollback my transactions?

    My Test table has two columns:

    id int not null
    somecolumn varchar(10))

    Now see my query:

    USE TRY
    BEGIN TRANSACTION T1
    INSERT INTO Test VALUES(7,’hi’);
    GO
    INSERT INTO Test VALUES(8,’hi’,’ABC’);
    GO
    PRINT @@ERROR
    if @@ERROR>0
    ROLLBACK TRANSACTION T1
    ELSE
    COMMIT TRANSACTION T1

    I know that my second query is wrong so I want transaction to rollback but it inserts the first query then shows this message:

    (1 row(s) affected)
    Msg 213, Level 16, State 1, Line 1
    Column name or number of supplied values does not match table definition.
    213

    Reply
  • Really Nice and Interesting one..

    Reply
  • IT IS VERY EASY TO UNDERSTAND BY EXAMPLE SO PLEASE GIVE AT LEAST TWO EXAMPLE FOR EACH EXPLANATION

    Reply
  • Thanks for the clear explanation

    Reply
  • Thanks Dave,
    What if it is an output parameter in a stored procedure? If a variable is an output parameter and has been set with a value (say an identity column using “scope_identity” on a table insert), and the transaction is rolled back, will the calling application still receive that value if it is not further manipulated within the stored procedure anytime after the rollback?

    Reply
  • Useful. Thanks a lot..

    Reply

Leave a Reply

Menu