I have two tables with Product and Purchase
- ProductID = 1
PName = tv
StockQty = 10
Purchase Table: -
PurchaseQty = 5 '
Product ID is Foreign Key in Purchase table.I am trying to write store proc so that whenever Purchase Quantity in added in purchase table.The main table product should also increment . For example: I add purchase quantity 5 .When I refresh product table it should display 15.I am getting only 10 in Product and whatever added in purchase.Please advise.
Below is my store proc
'CREATE PROCEDURE insertpurchase
@PID int ,
INSERT INTO [dbo].[Purchase] (PID ,PurchaseQty ) VALUES (@PID ,@PurchaseQty ) UPDATE [dbo].[Product] SET Product.StockQty = Product.StockQty + Purchase.PurchaseQty from Purchase END ' Can somebody guide what is wring with my query ?
Answer by Magnus Ahlkvist ·
First of all, you need to wrap your insert and your update in a transaction, otherwise you might end up with one of the statements working and not the other.
Second, you should not query the purchase table to find the quantity, that's already a parameter to the stored procedure, so you can use that one.
Third, you need a WHERE clause on the update statement, otherwise you will update all rows which will not be correct.
Here's my suggestion
CREATE PROCEDURE dbo.InsertPurchase @PID int, @PurchaseQty int AS BEGIN BEGIN TRY BEGIN TRAN INSERT dbo.Purchase ( PID, PurchaseQty ) VALUES ( @PID, @PurchaseQty ); UPDATE dbo.Product SET StockQty = StockQty + @PurchaseQty WHERE ProductID = @PID; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 ROLLBACK; THROW; END CATCH END