How to incr/decr column values based on passed parameters?-CodePudding

Looking to create a dynamic stored procedure for a single test-table where I can change values in one of the columns, based on arguments passed to it.

Example: I have one table where I want to be able to "move" movies (MovieID) from one store (StoreID) to another, and showcase that move in stock (Movies_in_stock). Once the stored procedure has run (and depending on arguments entered), it should show e.g. that 1 movie with MovieID = 100 in StoreID 1 has been moved to StoreID 2 - Movies_in_stock has subsequently decreased for StoreID = 1 and increased (plus a new row) for StoreID = 2.

Please see table changes below.

| StoreID | MovieID | Movies_in_stock |
|   1     |  100    |       10        |
|   2     |  200    |       20        |
|   3     |  300    |       50        |  
|   1     |  400    |      100        |
|   2     |  500    |        5        |

After running the stored procedure, I expect this data:

| StoreID | MovieID | Movies_in_stock |
|   1     |  100    |        9        |
|   2     |  200    |       20        |
|   3     |  300    |       50        |  
|   1     |  400    |      100        |
|   2     |  500    |        5        |
|   2     |  100    |        1        |

What I've done so far:

Set parameters:

MovieID = @MovieID, StoreID = @StoreID_From , StoreID = @StoreID_To, Movies_in_stock = @Stock (optional param)

Trying to figure out how to possibly combine CASE with UPDATE:

UPDATE Table1
SET Movies_in_stock = 
        (CASE WHEN @StoreID = x AND @StoreID = y 
                  THEN (Movies_in_stock = Movies_in_stock  /- @Movies_in_stock) 
                  ELSE 0 
         END)
WHERE @MovieID = z

Open and thankful for any suggestions/help/ideas/improvements received.

CodePudding user response：

What you are actually trying to do here is a merge.

Construct a virtual source table of changes: the From store gets a negative quantity.
Merge that into the existing table, inserting in cases where there is no row for that location and movie combination

CREATE OR ALTER PROCEDURE MoveStock
    @MovieID int,
    @StoreID_From int,
    @StoreID_To int,
    @StockToMove int = 1;
AS

WITH Source AS (
    SELECT StoreID = @StoreID_To, DiffQty = @StockToMove
    UNION ALL
    SELECT @StoreID_From, (-@StockToMove)
),
Target AS (
    SELECT *
    FROM Table1
    WHERE MovieID = @MovieID
      AND StoreID IN (@StoreID_From, @StoreID_To)  -- this line for performance only, try without
)
MERGE Target t
USING Source s
   ON s.StoreID = t.StoreID
WHEN MATCHED AND t.Movies_in_stock   s.DiffQty <= 0
    THEN DELETE  -- decide whether you want this condition
WHEN MATCHED THEN
    UPDATE SET Movies_in_stock  = s.DiffQty
WHEN NOT MATCHED THEN
    INSERT (StoreID, MovieID, Movies_in_stock)
    VALUES (S.StoreID, @MovieID, s.DifQty)
;