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)
19 Comments. Leave new
Yes, I like this kind of simple logic related articles. Please post them.
-Jay
Yes, really ur concept is very nice.
thanks
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
pls post some more like this .Although it is a simple example but there is logic inside it .
Hi pinal,
I like this type of examples lot from your’s
Thanks and Regards
sreeram
Nice examples,I have another one in
that demonstrates this issue.
Thanks
Dan
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
Simple and Powerful
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
It will work as long as both the variables and table variables are executed in the same scope
Hi Pinal,I liked this example very much .
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
Really Nice and Interesting one..
IT IS VERY EASY TO UNDERSTAND BY EXAMPLE SO PLEASE GIVE AT LEAST TWO EXAMPLE FOR EACH EXPLANATION
Thanks for the clear explanation
You are most welcome !!!
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?
Useful. Thanks a lot..