TRY, CATCH, ROLLBACK......or not

Question

TRY, CATCH, ROLLBACK......or not

Matthew24

SSCommitted

Points: 1736
More actions
June 17, 2011 at 5:17 pm

#253158

Hi
I am trying to get my head around the various options of handling errors in 2005. We are writing long SP's, and I need to ensure if something goes wrong half way through, then the tables are not left half finished.
The below I constructed to simply test the theory and my understanding:
BEGIN TRY
declare @t as table
(a int, b int, c int)
declare @1 as varchar set @1 = 'A'
insert into @t (a,b,c) values (1,1,1)
insert into @t (a,b,c) values (2,2,2)
insert into @t (a,b,c) values (3,3,3)
BEGIN TRANSACTION
insert into @t (a,b,c) values(4,4,4)
insert into @t (a,b,c) values(5,5,@1)
END TRY
BEGIN CATCH
ROLLBACK
END CATCH;
select * from @t
I'm expecting:
1,1,1
2,2,2
3,3,3
but I am also getting
4,4,4
I assumed the rollback would effectively negate the insert into @t (a,b,c) values(4,4,4).
What am I missing ?
Thanks
Matt

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table variables ignore explicit rollbacks. It's a feature of theirs. Try with temp tables, you'll see what you expect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

while i personally use Try catch block

There are other ways of exception handling such as using begin tran with if @@error also.

Jayanth Kurup[/url]

Matthew24 SSCommitted Points: 1736 More actions · Answer 3

GilaMonster (6/18/2011)
Table variables ignore explicit rollbacks. It's a feature of theirs. Try with temp tables, you'll see what you expect.

Ahhh. Thank you very much for taking the time to answer.

Matt