SQL server Rollback

Question

I created a transaction procedure and it all works except for some reason the rollback keeps displaying its error message and it is not allowing the insert to function properly. So now I am unsure of what I need to do to fix this when I comment out the section it all works perfectly it is only the IF rollback statement

Create Proc DP_Transaction
    @PartNumber Varchar (10)= NULL,
    @PartDescription Varchar(50) = Null,
    @ReorderLevel Decimal(5,0) =Null,
    @StockLevel decimal(5,0)= null,
    @StockOnOrder decimal(5,0) =null
as  

If @PartNumber IS NULL
begin
Print 'You must enter something for Partnumber' 
Print 'Order Not Processed'
print ' '
return
end

If @PartDescription IS NULL
 begin
Print 'You must enter something PartDescription' 
Print 'Order Not Processed'
print ' '
return
 end

If @ReorderLevel IS NULL
begin
Print 'A number must entered for ReorderLevel' 
Print 'Order Not Processed'
print ' '
return
end


If @StockLevel is Null 
Begin
print 'A number must be entered for StockLevel'
print 'Order Not Processed'
Print ''
Return
End

If @StockOnOrder is null
Begin
Print 'A number must be entered for StockOnOrder'
Print 'Order Not Processed'
Print ''
Return
End

Begin Transaction
Insert into InventoryPart
(Partnumber,PartDescription,ReorderLevel,StockLevel,StockOnOrder)
 Values(@PartNumber,@PartDescription,@ReorderLevel, @StockLevel, @StockOnOrder)


//This is where I am having the errors
If exists (Select PartNumber from InventoryPart where PartNumber = @PartNumber)
Begin
Print ' The Partnumber ' + @PartNumber+' is already in the InventoryPart table'
print ' you must select a different PartNumber'
Print ' Item not inserted'
print ''
Rollback

end
else

begin
Commit Transaction
print 'Part has been added'
print ''
print ''
End

user2989408 · Accepted Answer · 2014-04-29T22:11:59.830

2

Your query will not add any parts to the table. When you are checking for the existence of a part inserted in the previous statement, it will always be true and so will always Rollback your transaction.

There is no point in using a transactions in your example. You can simply do this using IF-ELSE.

Transaction, in my opinion, should be used only when they are absolutely needed.
Here you are simply trying to INSERT a part IF it does not exist in the table, ELSE you are displaying a message.

IF EXISTS (Select 1 from InventoryPart where PartNumber = @PartNumber)
BEGIN
    /*What you need to do*/
END
ELSE 
BEGIN
    /*What you need to do*/
END

edited Apr 29 '14 at 22:11

answered Apr 29 '14 at 22:01

user2989408

3,127
1
17
15

yeh the transaction was a requirement for this particualr situation. but your help is greatly appreiciated and my problem is solved! – user3380392 Apr 29 '14 at 22:12
One more thing, when you run `Select PartNumber from InventoryPart where PartNumber = @PartNumber`, it returns true as your previous insert query result is visible in current transaction. You need to check it first, then insert it or rollback, just like @user2989408 did. – zhongxiao37 Apr 30 '14 at 04:23

score 0 · Answer 2 · answered Apr 29 '14 at 22:01

It appears that the record created by the insert statement will always be rolled back in this procedure since you check for existence of a record after inserting it. you can fix the issue by using something like

-- replaces your transaction block:
-- only run insert if no record already exists
IF NOT EXISTS (Select * FROM InventoryPart where PartNumber = @PartNumber)
BEGIN
   INSERT INTO InventoryPart ....
END;

SQL server Rollback

2 Answers2