Store Proc
I have two tables with Product and Purchase
'Product Table:
- ProductID = 1
PName = tv
StockQty = 10
Purchase Table: -
PurchaseID =1
PID=1
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 ,
@PurchaseQty int,
@StockQty int
AS
BEGIN
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 ?
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
