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://www.SQLAuthority.com)
pinaldave
Yes, I like this kind of simple logic related articles. Please post them.
-Jay
Yes, really ur concept is very nice.
thanks
[...] My year old article - SQL SERVER - Effect of TRANSACTION on Local Variable - After ROLLBACK and After COMMIT 2) Discussion with SQL Server MVP - Jacob Sebastian - SQLAuthority News - Author Visit - SQL Hour [...]
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