Transaction coding style
in [Programming SQL]
|
From: Uri Dimant on 25 Mar 2010 09:38 BEGIN TRY SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRANSACTION DELETE FROM dbo.Address WHERE UserId = @userid; IF(@@ROWCOUNT = 0) SET @statuscode = 20; ELSE SET @statuscode = 100; COMMIT TRANSACTION; END TRY BEGIN CATCH IF(@@TRANCOUNT > 0)ROLLBACK TRANSACTION; EXEC dbo.Error_ErrorHandler; SET @statuscode = 99; END CATCH "Senna" <Senna(a)discussions.microsoft.com> wrote in message news:37F62446-44D5-4A36-B548-1C92D6F3AA82(a)microsoft.com... > Yeah, I see what you mean, that makes more sense. Thanks. > > Any input regarding my first question?
From: Senna on 25 Mar 2010 14:07 Thanks Uri. Have asked around other forums too and I think I found an alternative that works fine. SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION DELETE FROM dbo.Address WHERE UserId = @userid; DELETE FROM dbo.Customer WHERE UserId = @userid; SET @statuscode = 100; COMMIT TRANSACTION; END TRY BEGIN CATCH IF(@@TRANCOUNT > 0) BEGIN IF (XACT_STATE() IN (0,-1)) ROLLBACK TRANSACTION; ELSE IF (XACT_STATE() = 1) COMMIT TRANSACTION; END SET @statuscode = 99; EXEC dbo.Error_ErrorHandler; END CATCH "Uri Dimant" wrote: > BEGIN TRY > SET NOCOUNT ON > SET XACT_ABORT ON > > BEGIN TRANSACTION > DELETE FROM dbo.Address WHERE UserId = @userid; > > IF(@@ROWCOUNT = 0) > SET @statuscode = 20; > ELSE > SET @statuscode = 100; > COMMIT TRANSACTION; > END TRY > BEGIN CATCH > IF(@@TRANCOUNT > 0)ROLLBACK TRANSACTION; > EXEC dbo.Error_ErrorHandler; > SET @statuscode = 99; > END CATCH > > > > > > "Senna" <Senna(a)discussions.microsoft.com> wrote in message > news:37F62446-44D5-4A36-B548-1C92D6F3AA82(a)microsoft.com... > > Yeah, I see what you mean, that makes more sense. Thanks. > > > > Any input regarding my first question? > > > . >
From: Tom on 25 Mar 2010 14:59 On Mar 25, 2:07 pm, Senna <Se...(a)discussions.microsoft.com> wrote: > Thanks Uri. > > Have asked around other forums too and I think I found an alternative that > works fine. > > SET NOCOUNT ON > SET XACT_ABORT ON > BEGIN TRY > BEGIN TRANSACTION > DELETE FROM dbo.Address WHERE UserId = @userid; > DELETE FROM dbo.Customer WHERE UserId = @userid; > > SET @statuscode = 100; > COMMIT TRANSACTION; > END TRY > BEGIN CATCH > IF(@@TRANCOUNT > 0) > BEGIN > IF (XACT_STATE() IN (0,-1)) > ROLLBACK TRANSACTION; > ELSE IF (XACT_STATE() = 1) > COMMIT TRANSACTION; > END > SET @statuscode = 99; > EXEC dbo.Error_ErrorHandler; > END CATCH > > > > "Uri Dimant" wrote: > > BEGIN TRY > > SET NOCOUNT ON > > SET XACT_ABORT ON > > > BEGIN TRANSACTION > > DELETE FROM dbo.Address WHERE UserId = @userid; > > > IF(@@ROWCOUNT = 0) > > SET @statuscode = 20; > > ELSE > > SET @statuscode = 100; > > COMMIT TRANSACTION; > > END TRY > > BEGIN CATCH > > IF(@@TRANCOUNT > 0)ROLLBACK TRANSACTION; > > EXEC dbo.Error_ErrorHandler; > > SET @statuscode = 99; > > END CATCH > > > "Senna" <Se...(a)discussions.microsoft.com> wrote in message > >news:37F62446-44D5-4A36-B548-1C92D6F3AA82(a)microsoft.com... > > > Yeah, I see what you mean, that makes more sense. Thanks. > > > > Any input regarding my first question? > > > .- Hide quoted text - > > - Show quoted text - if XACT_STATE() = 0 and you issue a rollback you should get an error that says there is nothing to rollback. I also suspect that seldom when you get to a catch block will you ever want to commit. If that were the case you would not have issued the begin transaction.
From: Bob Barrows on 25 Mar 2010 15:03 Senna wrote: > Thanks Uri. > > Have asked around other forums too and I think I found an alternative > that works fine. > > BEGIN CATCH > IF(@@TRANCOUNT > 0) There could be a problem here. I remember an old white paper that recommended setting a variable to @@TRANCOUNT at the beginning of a procedure. Then, when handling errors, it recommended comparing @@TRANCOUNT to the local variable when deciding whether or not to rollback. -- HTH, Bob Barrows
From: Senna on 25 Mar 2010 18:21
Ok, will drop the XACT_STATE() part. Thanks Tom "Tom" wrote: > if XACT_STATE() = 0 and you issue a rollback you should get an error > that says there is nothing to rollback. > > I also suspect that seldom when you get to a catch block will you ever > want to commit. If that were the case you would not have issued the > begin transaction. > . > |