Try and Catch

Question

Try and Catch

tripri

SSCommitted

Points: 1584
More actions
November 4, 2011 at 8:21 am

#262325

Hi,
I'm using try and catch for my query.
I got two tables
Main and MainSub1
and pulling data from one table to another
and my query looks like
Declare @Id int
Select @Id=ID from Main where Id=4
begin try
Begin Tran ;
if @Id is not null
begin
insert into MainSub1(name)
select name from Main where Id=@Id
end
Commit Tran;
end try
begin catch
Declare @Error varchar(500)
Set @Error=ERROR_MESSAGE()
Select @Error
Update Main set Record=@Error
where ID=@Id
end catch
when i execute that query i will be getting an error and it will be updating my main table. When i execute that i got success message stating that my 0 row(s) affected(no insert done as error) and 1 row(s) is affected(update Main table)
Now when i try to do Select * from Main it is executing for a while and getting a lock erro. Can some one tell me whats the issue with that as i'm confused.
Can i use Update Statement in Catch Block?

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

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

toddasd SSCertifiable Points: 6298 More actions · Answer 1

You're getting a lock error on the select because you are not rolling back the transaction opened earlier. But there is no need to use a transaction when there is but one statement firing.

And why use a try/catch? Can't this whole thing be reduced to

insert into MainSub1(name)

select name from Main where Id=4

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

tripri SSCommitted Points: 1584 More actions · Answer 2

Thank you so much toddasd

I should kick myself for not lookin into that.

Actually that is just sample statement according to my original query.

toddasd SSCertifiable Points: 6298 More actions · Answer 3

You are welcome. It's is one of those deals -- having an open transaction and nothing else works -- that causes so much trouble, that you never forget that type of situation. It happened to me on a production system. :crying:

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.