Hello friend, you may try this STORED PROC written in SQl Server:
CREATE PROCEDURE UpdateStock
@ProductID int,
@OrderQuantity int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TotalStock INT
SET @TotalStock = (Select SUM(Quantity) from PRODUCT_INVENTORY where ProductID = @ProductID)
IF @TotalStock < @OrderQuantity
BEGIN
PRINT 'Stock not available'
RETURN -1
END
DECLARE @InventoryID INT
DECLARE @QuantityInHand INT
DECLARE @GetInventoryID CURSOR
SET @GetInventoryID = CURSOR FOR
SELECT ID, Quantity
FROM PRODUCT_INVENTORY
WHERE ProductID = @ProductID
ORDER BY ReceivedDate
OPEN @GetInventoryID
FETCH NEXT
FROM @GetInventoryID INTO @InventoryID, @QuantityInHand
WHILE @@FETCH_STATUS = 0
BEGIN
IF @OrderQuantity = 0
BEGIN
PRINT 'Updated Successfully'
RETURN 1
END
IF @OrderQuantity <= @QuantityInHand
BEGIN
UPDATE PRODUCT_INVENTORY
SET Quantity = Quantity - @OrderQuantity
WHERE ID = @InventoryID
SET @OrderQuantity = 0
END
ELSE
BEGIN
UPDATE PRODUCT_INVENTORY
SET Quantity = 0
WHERE ID = @InventoryID
SET @OrderQuantity = @OrderQuantity - @QuantityInHand
END
FETCH NEXT
FROM @GetInventoryID INTO @InventoryID, @QuantityInHand
END
CLOSE @GetInventoryID
DEALLOCATE @GetInventoryID
END
Note: The above example is only for demo purpose. Before you use in any real life program, make sure that you implement Exception handling, Transaction properly.