2

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
vahid moradpour
  • 159
  • 1
  • 4
  • 15
  • 1
    this link is similar your question. https://stackoverflow.com/questions/20709351/dynamic-stored-procedure – Maysam Razzaghi Apr 17 '21 at 08:27
  • 1
    `@DateFactor char(10)`? This just is huge problem if the parameter represents an actual date. But what is your goal? Surely your purchase tables and procedures are in a different schema. What do you expect to gain while also adding confusion due to your schema structure? If you cannot write this dynamic sql yourself, I would argue that you are far safer to leave things as they are. The risk is not worth whatever you hope to gain. – SMor Apr 17 '21 at 12:05
  • I used this type of format to save Persian Date. For example: @DateFactor="1399/01/12" – vahid moradpour Apr 17 '21 at 18:13
  • I used Dynamic SP for this problem – vahid moradpour Jul 26 '21 at 13:44
  • Thank you Mr SMor for Answer – vahid moradpour Jul 26 '21 at 13:51

0 Answers0