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-29 22:11:59Z

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

yeh the transaction was a requirement for this particualr situation. but your help is greatly appreiciated and my problem is solved!
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.

Jeremy · Accepted Answer · 2014-04-29 22:01:32Z

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;

Collectives™ on Stack Overflow

SQL server Rollback

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related