Is exiting a stored procedure before using a commit harmful?
I tried searching for this but I have not found anything. If I have something like:
CREATE PROCEDURE QQ AS BEGIN TRANSACTION BEGIN TRY -- return early and skip commit here IF (Condition = true) RETURN 0 COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK END CATCH
What will happen with the transaction?
It will stay open until you COMMIT, ROLLBACK, or the connection is closed and that spid is killed.
This will block other processes and cause all kinds of other issues.
As a rule always run a check like below in your CATCH block to make sure you close up cleanly.
IF @@TRANCOUNT > 0 ROLLBACK
WHILE @@Trancount > 0 BEGIN ROLLBACK END