Using this stored procedure, I deduct the inventory of Commodity based on sales invoices. I also have a stored procedure to add inventory based on purchase invoices that have similar structures.
How can I create a dynamic stored procedure that does not need the two other stored procedures with the same structure?
ALTER PROCEDURE [Sales].[Fa_Update_FactorSell_changeValue]
@IDFactor int,
@DateFactor char(10)
AS
UPDATE c
SET c.Count = (CASE
WHEN c.Units = f.IDSize
THEN c.Count - (SELECT ValueCommodity FROM Factor.FactorSellDetails
WHERE IDCommodity =c.IDCommodity
AND IDFactor = @IDFactor
AND DateFactor = @DateFactor)
WHEN cs.IDSizeSub = f.IDSize
THEN c.Count - (f.ValueCommodity * (SELECT ValuesSub FROM Sales.WarehouesSizeSub
WHERE IDCommodity = c.IDCommodity
AND IDSizeSub = cs.IDSizeSub))
END)
FROM Sales.CommodityStock c
INNER JOIN Factor.FactorSellDetails f ON f.IDFactor = @IDFactor
AND f.DateFactor = @DateFactor
AND f.IDCommodity = c.IDCommodity
INNER JOIN Sales.Commodity cs ON cs.ID = c.IDCommodity
AND c.IDWarehoues = cs.IDWarehosues