Conditionally begin a transaction depending on transaction state

bjh1977
bjh1977 used Ask the Experts™
on
Hi,

Is this a good way of conditionally wrapping table updates in a transaction:


CREATE PROCEDURE dbo.spUpdateSomeData
      @DeliveryId                  UNIQUEIDENTIFIER,
      @InvoiceId                  UNIQUEIDENTIFIER
AS

BEGIN

      SET NOCOUNT ON

      DECLARE @CALLER_IN_TRANSACTION_STATE TINYINT
      
      IF @@TRANCOUNT > 0
      BEGIN
            SET @CALLER_IN_TRANSACTION_STATE = 1
      END
      ELSE
      BEGIN
            SET @CALLER_IN_TRANSACTION_STATE = 0
            
            --AS THE CALLING PROC HAS NOT BEGUN A TRANSACTION, START ONE HERE..
            BEGIN TRAN
                  
      END

      UPDATE MYTABLE
      SET SOME_COLUMN = 1
      WHERE SOME_OTHER_COLUMN <> 0

      IF @@ERROR <> 0
            GOTO ErrHan
            
Finally:
      IF @@TRANCOUNT > 0 AND @CALLER_IN_TRANSACTION_STATE = 0
            COMMIT TRANSACTION
      
      RETURN 0

ErrHan:
      IF @@TRANCOUNT > 0 AND @CALLER_IN_TRANSACTION_STATE = 0
            ROLLBACK TRANSACTION
            
      RETURN -1
END

GO

      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Couple of things. What are you trying to gain with this? Also, the label "Finally:" is not referenced anywhere.

I'm not entirely sure what you're trying to acheive with this. There is nothing technically wrong with what you're doing and no reason for you not to do it but it seems to me to me a convoluted way to go about transaction handling.

Lee

Author

Commented:
Hi lsavidge,

I took this snippet from an existing stored proc hence the unreferenced "Finally" label.

The reason I asked this question was because sometimes the sp could be called from a web page, or it could be called from another stored procedure which in turn is called from a web service.  

My aim was to allow the calling proc to deal with committing or rolling back updates done in this sp if it was actually executed from another sp (in this case there would be a whole load of delete/update/inserts done by the calling proc which should also be part of the transaction).  On the other hand, if this sp was called directly via ADO then i still wanted it to be done in a transaction.



It's as good a way as any. I don't see any particular issues with it. As there seem to be multiple callee's for this sp it might be difficult to test all scenarios easily but the approach seems sound enough to me.

Lee

Author

Commented:
great - thanks for the sanity check!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial