Conditionally begin a transaction depending on transaction state
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_STA
IF @@TRANCOUNT > 0
BEGIN
SET @CALLER_IN_TRANSACTION_STA
END
ELSE
BEGIN
SET @CALLER_IN_TRANSACTION_STA
--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_STA
COMMIT TRANSACTION
RETURN 0
ErrHan:
IF @@TRANCOUNT > 0 AND @CALLER_IN_TRANSACTION_STA
ROLLBACK TRANSACTION
RETURN -1
END
GO
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_STA
IF @@TRANCOUNT > 0
BEGIN
SET @CALLER_IN_TRANSACTION_STA
END
ELSE
BEGIN
SET @CALLER_IN_TRANSACTION_STA
--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_STA
COMMIT TRANSACTION
RETURN 0
ErrHan:
IF @@TRANCOUNT > 0 AND @CALLER_IN_TRANSACTION_STA
ROLLBACK TRANSACTION
RETURN -1
END
GO
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great - thanks for the sanity check!
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