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 (http://blog.SQLAuthority.com)

About these ads

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

  1. Pingback: SQL SERVER - Transaction and Local Variables - Swap Variables - Update All At Once Concept Journey to SQL Authority with Pinal Dave

  2. 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

    Like

  3. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  4. 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

    Like

  5. 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

    Like

  6. Pingback: SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video « SQL Server Journey with SQL Authority

  8. 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

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s