question

Zaxk avatar image
Zaxk asked

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 ?

stored-proceduressqlserver2012
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.