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
Declare @PartitionID Int
If Exists(Select PartitionName From Partition Where PartitionName = @PartitionName)
RAISERROR ('Partition name is already in use.', 16, 1)
Select @PartitionID = Max(PartitionID) + 1 From Partition
Insert Into Partition (PartitionID, PartitionName) Values (@PartitionID, @PartitionName)
IF (@@error = 0)