Solved

Transactions and Raiserror

Posted on 2003-12-09
8
1,060 Views
Last Modified: 2008-02-20
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
Comment
Question by:daveamour
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Accepted Solution

by:
kathmacmahon earned 25 total points
ID: 9905794
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9905820
@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
 
LVL 8

Expert Comment

by:dishanf
ID: 9910213
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9910227
SET XACT_ABORT ON
this will rollback
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Author Comment

by:daveamour
ID: 9910697
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9913685
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
 
LVL 19

Author Comment

by:daveamour
ID: 9913700
Ok thanks.  I think I get the idea now.  Thanks again for your help.
0
 
LVL 19

Author Comment

by:daveamour
ID: 9921621
Sorry Just realised I never actually gave the points.  Sorry!  All done now.

Dave
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now