May 3, 2007 at 11:51 am
We have a stored procedure that uses try catch block and transactions. The catch block will rollback the transaction. The problem we encounter is if we have a parsing error (table doesn't exist or column name changed or removed) then the stored procedure exits and doesn't ever go to the catch block so we have an uncommitted transaction which of course causes several problems. Is there an easy way in a stored procedure to make sure the transaction is rolled back if those certain errors occur? Thanks in advance
May 3, 2007 at 12:49 pm
Try/catch should pick that up. I'm thinking there is something else causing it to leave the transaction open. If you look under try...catch in BOL there are some examples of using @@Trancount and xact_abort, see samples B and C.
The below works for me, the proc in question does not exist, and the error reflects this.
-- proc does not exist
BEGIN TRY
Begin tran
EXEC usp_ExampleProc2
commit
END TRY
BEGIN CATCH
select
ERROR_NUMBER()
AS ErrorNumber,
ERROR_MESSAGE()
AS ErrorMessage
rollback tran
END CATCH;
2812 Could not find stored procedure 'usp_ExampleProc2'.
May 7, 2007 at 7:14 am
JMeyer32,
You must be getting errors with severity 20-25, as they terminates the database connection CATCH block could not handle them.
Regards
Shrikant Kulkarni
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply