Sample Header Ad - 728x90

Best practices for committing a transaction in SQL Server where TRY CATCH is used

7 votes
2 answers
27174 views
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?. For example, is option A or option B the correct approach or are they subjective choices? **Option A** CREATE PROCEDURE DummyProc BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH **Option B** CREATE PROCEDURE DummyProc BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH IF @@trancount > 0 COMMIT TRANSACTION In Option B, is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?
Asked by user20358 (213 rep)
Mar 25, 2019, 10:15 PM
Last activity: Mar 26, 2019, 12:15 PM