Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transactions and Raiserror

Posted on 2003-12-09
8
Medium Priority
?
1,068 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 75 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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