Executing a stored procedure inside BEGIN/END TRANSACTION

Question

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

I didn't know if it worked like try/catches in C#.

score 52 · Accepted Answer · answered Oct 07 '08 at 20:11

52

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

answered Oct 07 '08 at 20:11

Blorgbeard

101,031
48
228
272

6

This isn't completely true. Actually, operations on table variables are outside the transaction scope; a rollback does not affect any changes that have been made in your table variable. – Chris Wuestefeld Sep 03 '09 at 15:10
6

Well, I meant everything you do *to the database* - I guess I assumed it was obvious that variables are not affected by transactions - although I suppose it's a bit less intuitive with table variables than scalar variables. – Blorgbeard Sep 03 '09 at 22:20
1

What if the Sp called inside the outer transation calls commit transation? Will the inner transaction be rolled back if the outer transaction calls rollback after the inner is commited? – OpenCoderX Sep 02 '15 at 14:20
8

Yes, that is how nested transactions work. Committing the inner transaction [does nothing](http://stackoverflow.com/questions/851441/nested-transactions-in-sql-server); only the outer transaction is actually ever committed or rolled back. – Blorgbeard Sep 02 '15 at 20:51

score 14 · Answer 2 · edited Nov 09 '11 at 01:05

14

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

    BEGIN TRY
       BEGIN TRANSACTION

       DO SOMETHING

       COMMIT
    END TRY
    BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK

      -- 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

edited Nov 09 '11 at 01:05

knocte

16,941
11
79
125

answered Oct 07 '08 at 20:16

Miles

5,646
18
62
86

This works! thanks! Not like the solution by KM in http://stackoverflow.com/questions/2715184/tsql-create-a-stored-proc-inside-a-transaction-statement which throws a network related error – knocte Nov 08 '11 at 16:00

score 6 · Answer 3 · answered Oct 07 '08 at 20:11

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.

James · Answer 4 · 2008-10-07T21:51:06.917

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

Specifically this:

IF @@TRANCOUNT > 0 ROLLBACK

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

IF(@@TRANCOUNT = 1) ROLLBACK TRAN
ELSE IF(@@TRANCOUNT > 1) COMMIT TRAN
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.

score 3 · Answer 5 · edited May 23 '17 at 12:09

3

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:

edited May 23 '17 at 12:09

Community

1
1

answered Oct 08 '08 at 08:57

kristof

52,923
24
87
110

nice articles, but i think it might be an old one as it was wrote in year 2004, nonetheless still there is a lot of relevance on subject – Mitesh Vora May 17 '18 at 10:57

score 1 · Answer 6 · answered Oct 07 '08 at 20:14

1

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.

answered Oct 07 '08 at 20:14

Ben Hoffstein

102,129
8
104
120

score 1 · Answer 7 · answered Oct 07 '08 at 22:23

1

@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.

answered Oct 07 '08 at 22:23

Blorgbeard

101,031
48
228
272

Executing a stored procedure inside BEGIN/END TRANSACTION

7 Answers7

Linked