• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

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


0
daveamour
Asked:
daveamour
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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

0
 
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*
0
 
Dishan FernandoSoftware Engineer / DBACommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dishan FernandoSoftware Engineer / DBACommented:
SET XACT_ABORT ON
this will rollback
0
 
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
0
 
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
0
 
daveamourAuthor Commented:
Ok thanks.  I think I get the idea now.  Thanks again for your help.
0
 
daveamourAuthor Commented:
Sorry Just realised I never actually gave the points.  Sorry!  All done now.

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now