How to convert two stored procedure to a dynamic stored procedure

Viewed 81

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
0 Answers
Related