Why would you commit a transaction in the catch section?
Microsoft has the following example for try ... catch in tsql:
USE AdventureWorks;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
Source for the above example: Using TRY ... CATCH in Transact-SQL
I don't understand why you want to commit the transaction that resulted in the exception. It seems like at least 9 times out of 10 you would like IF (XACT_STATE ())! = 0 ROLLBACK TRANSACTION. Why do you want partial success over a clean list?
a source to share
This pattern is completely wrong. It is understood that you have a try-catch block to handle duplicate keys and recover and perform an alternate operation (perhaps update instead of insert). But to have a COMMITS code block on success, but leaves the transaction open in case of an error, and even more so silently swallows the error - it's just mind blowing. This piece of code is a big big worm of worms.
My site has a sample procedure template that handles errors and transactions correctly , allowing an inline transaction to be restored and continue on error correctly . Typical examples where you want processing to be recovered in case of an error is in batch processing: as you move through the batch, you store a point before each record, then try to process it. If the processing fails, you save the entry to the failed table and continue without losing the whole batch.
Update
LOL, I missed the commit in catch too. Then not as bad as my original comment. I still prefer my pattern, which uses a savepoint and allows nested transactions.
a source to share