Solved

Transactions and Raiserror

Posted on 2003-12-09
8
1,064 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Dishan Fernando
ID: 9910213
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9910227
SET XACT_ABORT ON
this will rollback
0
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 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