Transactions and Raiserror

I'm not totally confident in what I'm doing but I am trying to use transactions and raiserror.  I am having some problems though.

When I raise an error then my transaction isn't being rolled back and I am geting the following error message.

Error adding partition: Partition name is already in use.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

When an error is generated by an incorrect table name for example which I get by editing the stored procedure so that a table name is invalid then I am getting the following error message:

Error adding partition: Invalid object name 'Partitionxxxx'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Can anyone help explain what is happening and what I have done wrong?

My stored procedure is as follows:

CREATE PROCEDURE spAddPartition

@PartitionName VarChar(50)

AS

BEGIN TRAN

Declare @PartitionID Int

If Exists(Select PartitionName From Partition Where PartitionName = @PartitionName)
Begin
      RAISERROR ('Partition name is already in use.', 16, 1)
      ROLLBACK TRANSACTION
End

Select @PartitionID = Max(PartitionID) + 1 From Partition

Insert Into Partition (PartitionID, PartitionName) Values (@PartitionID, @PartitionName)

IF (@@error = 0)
BEGIN
      COMMIT TRANSACTION
      RETURN(0)
END

ELSE

BEGIN
      GOTO ErrorHandler
END

ErrorHandler:

ROLLBACK TRANSACTION
RETURN (1)
GO


LVL 19
daveamourAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kathmacmahonCommented:
CREATE PROCEDURE spAddPartition

@PartitionName VarChar(50)

AS
Declare @PartitionID Int
If Exists(Select PartitionName From Partition Where PartitionName = @PartitionName)
Begin
     RAISERROR ('Partition name is already in use.', 16, 1)
     GOTO FINISH
End

Select @PartitionID = Max(PartitionID) + 1 From Partition

BEGIN TRAN

Insert Into Partition (PartitionID, PartitionName) Values (@PartitionID, @PartitionName)

IF (@@error = 0)
BEGIN
     COMMIT TRANSACTION
     GOTO Finish
END

ROLLBACK TRANSACTION
RETURN

Finish:
RETURN

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DexstarCommented:
@daveamour:

> Can anyone help explain what is happening and what I have done wrong?

I don't see any need for transactions on this one.  Just check the table for the record, raise an error if you find a record, and insert the record if you don't.

The only time you need transactions is if you are going to make multiple changes to the database, and you want an "all or nothing" situation, where you can undo the earlier changes after a subsequent one fails.

Here is the code I would use:
      CREATE PROCEDURE spAddPartition
      (
            @PartitionName VarChar(50)
      )
      AS
      
      DECLARE @PartitionID INT
      
      IF EXISTS(SELECT PartitionName FROM Partition WHERE PartitionName = @PartitionName)
            BEGIN
                  RAISERROR( 'Partition name is already in use.', 16, 1)
                  RETURN -1
            END
      ELSE
            BEGIN
                  SELECT @PartitionID = MAX(PartitionID) + 1 FROM Partition
                  INSERT INTO Partition (PartitionID, PartitionName) VALUES (@PartitionID, @PartitionName)
                  IF (@@ERROR = 0) THEN
                        RETURN 0
                  ELSE
                        RETURN -2
            END
      GO

Hope That Helps,
Dex*
Dishan FernandoSoftware Engineer / DBACommented:
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dishan FernandoSoftware Engineer / DBACommented:
SET XACT_ABORT ON
this will rollback
daveamourAuthor Commented:
Ok thanks guys all helpful stuff.

Points to kath since her example works and helped me see what was wrong with my code - I wasn't returning after raising an error!

Thanks for your help too dexstar.  This stored procedure is just in its infancy - it is going to have lots more inserts etc so thats why I need Transactions - I am just getting the plumbing in place first.

Thanks again

Dave
DexstarCommented:
Okay, then, I can totally understand that.  Here is the formula that I follow:

DECLARE @nResult INT

-- Start the transaction
BEGIN TRANSACTION

-- Make First Change to the database
INSERT SomeTable( Blah ) VALUES('BlahBlah')
-- Check for error
SELECT @nResult = @@ERROR
IF @nResult <> 0
     GOTO OnError

-- Make 2nd Change to the database
UPDATE SomeOtherTable
SET Blah = 'DoubleBlah'
WHERE ID = 1234
-- Check for error
SELECT @nResult = @@ERROR
IF @nResult <> 0
     GOTO OnError

--- Make Another Change
DELETE FROM YetAnotherTable WHERE ID = 4321
-- Check for error
SELECT @nResult = @@ERROR
IF @nResult <> 0
     GOTO OnError

-- Save Changes
COMMIT TRANSACTION
RETURN 0

OnError:
     ROLLBACK TRANSACTION
     RETURN @nResult
daveamourAuthor Commented:
Ok thanks.  I think I get the idea now.  Thanks again for your help.
daveamourAuthor Commented:
Sorry Just realised I never actually gave the points.  Sorry!  All done now.

Dave
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.