I have recently starting making use of the TRY?CATCH statement in SQL Server 2005 - simple example in attached code.
Now I have run into a small problem... if the executed SP uses a transaction, and if the error occurs within that transaction, then after the catch block I end up with a "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." error.
So I thought I'd be clever and check for uncommitted transactions and close them, as per the second example in the attached code.
But then I got this error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.".
So it appears that even though the transactions are still open, because I am outside the scope of the begin trans I am unable to close it. I assume I could add a try/catch block within the transaction and then roll it back. But to me that defeats the purpose of using the try/catch - I purposely placed my try/catch at the point where I would like to handle the errors - and yet if there is still an open transaction my try/catch doesn't work unless I then put a try/catch inside every transaction that is called within my outer try/catch block (and there are a lot of these... I have simplified by showing a single SP... in fact there are a lot of them called in this context).
Have I misunderstood how to use this? Is there a way to accomplish what I am trying to accomplish?
-- Example 1
-- Example 2
declare @TransCount int
select @TransCount = @@trancount
while @TransCount > 0
set @TransCount = @TransCount - 1