Is exiting a stored procedure before using a commit harmful?

Question

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?

Answer 1

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

or

WHILE @@Trancount > 0
BEGIN
   ROLLBACK
END

Answer 2

Just to be sure, I would perform a ROLLBACK before you exit the statement. Depending of your database software, they could perform an auto commit on exit.