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