Executing a stored procedure inside BEGIN/END TRANSACTION

If I create a Stored Procedure in SQL and call EXEC spStoredProcedure within the BEGIN/END TRANSACTION, does this other stored procedure also fall into the transaction?

I didn’t know if it worked like try/catches in C#…

  • Select columns from result set of stored procedure
  • Upload CSV file to SQL server
  • T:SQL: select values from rows as columns
  • Paging with 3 sub-queries with 1 connection vs 2 Queries with 2 connections
  • how to pass multiple unique identifiers to stored procedure
  • Possible to view T-SQL syntax of a stored proc-based SqlCommand?
  • 7 Solutions collect form web for “Executing a stored procedure inside BEGIN/END TRANSACTION”

    Yes, everything that you do between the Begin Transaction and Commit (or Rollback) is part of the transaction.

    Sounds great, thanks a bunch. I ended up doing something like this (because I’m on 05)

        BEGIN TRY
           DO SOMETHING
        END TRY
          IF @@TRANCOUNT > 0
          -- Raise an error with the details of the exception
          DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
          SELECT @ErrMsg = ERROR_MESSAGE(),
                 @ErrSeverity = ERROR_SEVERITY()
          RAISERROR(@ErrMsg, @ErrSeverity, 1)
        END CATCH

    I believe in MS SQL Server the stored procedure execution would happen within the transaction, but be very careful with this. If you have nested transactions (ie, transaction outside of the stored procedure and a different transaction inside the stored procedure), a rollback will affect ALL of the transactions, not just the nearest enclosing transaction.

    As Chris mentioned, you should be careful about rolling the transaction back.

    Specifically this:


    is not always what you want. You could do something like this

    RETURN @error

    This way, the calling proc can inspect the return value from the stored procedure and determine if it wants to commit anyways or continue to bubble up the error.

    The reason is that ‘COMMIT’ will just decrement your transaction counter. Once it decrements the transaction counter to zero, then an actual commit will occur.

    Yes, all nested stored procedure calls are included in the scope of the transaction. If you are using SQL Server 2005 or greater, you can use Try…Catch as well. Here is more detail on that.

    @Chris, I did not know that.

    When googling for more info, I came across this – you can set ‘savepoints’, which can be rolled back to without rolling back the whole transaction.

    Could be useful in this situation.

    As Chris and James mentioned, you need to be careful when dealing with nested transactions.
    There is a set a very good articles on the subject of transactions written by Don Peterson on SQL Server Central , I would recommend having a read of those:

    Here there are:

    • part 1
    • part 2
    • part 3
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.