CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1185775
user1185775

Reputation: 111

RETURN inside a transaction with TRY-CATCH block

I have the following code, and I am wondering, as a generic example, if the transaction is left open if it exits with RETURN. 

BEGIN TRANSACTION
    BEGIN TRY
        IF NOT EXISTS(SELECT 1 FROM dbo.tblProducts WHERE intProductID = @intProductID)
            BEGIN
                SELECT 'Product does not exists' AS strMessage
                RETURN
            END

        UPDATE dbo.tblProducts SET
            curPrice = 10
        WHERE
            intProductID = @intProductID

        SELECT 'Success' AS strMessage

    END TRY 

    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS strMessage
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
    END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION

Upvotes: 7

Views: 25851

Answers (4)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Please see the Stored Procedure template that I posted in the following DBA.StackExchange answer as it address the use of TRY / CATCH with Transactions, and it handles nested calls, even if the Transaction is opened in the app layer:

Are we required to handle Transaction in C# Code as well as in Store procedure

Upvotes: 0

Eduardo
Eduardo

Reputation: 182

This code works with nested transactions:

BEGIN TRY
   IF @@TRANCOUNT > 0
      SAVE TRANSACTION MyTransactionName
   ELSE
       BEGIN TRANSACTION MyTransactionName

    IF NOT EXISTS(SELECT 1 FROM dbo.tblProducts WHERE intProductID = @intProductID)
        BEGIN
            SELECT 'Product does not exists' AS strMessage
            ROLLBACK TRANSACTION MyTransactionName
            RETURN
        END

    UPDATE dbo.tblProducts SET
        curPrice = 10
    WHERE
        intProductID = @intProductID

    SELECT 'Success' AS strMessage

    COMMIT TRANSACTION MyTransactionName
END TRY 

BEGIN CATCH
    SELECT ERROR_MESSAGE() AS strMessage
    ROLLBACK TRANSACTION MyTransactionName
END CATCH

Upvotes: 2

PseudoToad
PseudoToad

Reputation: 1574

I just tried this by running the code above then checking SELECT @@TRANCOUNT and then by attempting a ROLLBACK. After the return, @@TRANCOUNT was 1 and I was able to rollback the transaction successfully indicating that the transaction is left open.

Upvotes: 3

Pankaj
Pankaj

Reputation: 10095

It should be like Below

BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN TRANSACTION
        IF NOT EXISTS(SELECT 1 FROM dbo.tblProducts 
                          WHERE intProductID = @intProductID)
        BEGIN
            SELECT 'Product does not exists' AS strMessage
            Rollback TRan
            RETURN
        END

        UPDATE dbo.tblProducts SET
        curPrice = 10
        WHERE
        intProductID = @intProductID

        SELECT 'Success' AS strMessage
    COMMIT TRAN
END TRY 

BEGIN CATCH
    SELECT ERROR_MESSAGE() AS strMessage
    ROLLBACK TRANSACTION
END CATCH

Upvotes: 8

Related Questions