使用Table参数插入语句时，基于上一行插入值

I am trying to update column value based on previous row with condition when using table type parameter to insert record.

我正在尝试使用表类型参数插入记录时基于前一行的条件更新列值。

ALTER PROCEDURE [dbo].[Crud_StockTransaction]
    (@p_StockTransaction UDT_StockTransaction READONLY)   -- table-valued parameter
AS
BEGIN
    INSERT INTO StockTransaction (TransactionType, TransactionId, TransactionItemId, OpeningQuantity, ClosingQuantity)
        SELECT 
            TransactionType, TransactionId, TransactionItemId,
            (SELECT TOP 1 ISNULL(ClosingQuantity, 0)
             FROM StockTransaction 
             WHERE TransactionType = A.TransactionType AND TransactionItemId = A.TransactionItemId),
            --OpeningQuantity,
            CASE A.TransactionType 
               WHEN 1 
                  THEN ((SELECT TOP 1 ISNULL(ClosingQuantity, 0) 
                         FROM StockTransaction 
                         WHERE TransactionType = A.TransactionType 
                           AND TransactionItemId = A.TransactionItemId) + A.Quantity)
                WHEN 2 
                   THEN ((SELECT TOP 1 ISNULL(ClosingQuantity, 0) 
                          FROM StockTransaction 
                          WHERE TransactionType = A.TransactionType 
                            AND TransactionItemId = A.TransactionItemId) - A.Quantity)
            END,
            --ClosingQuantity,
        FROM 
            @p_StockTransaction

END

This seems to be complex, is there any better way to do the same?

这似乎很复杂，有没有更好的方法来做同样的事情？

1 个解决方案

#1

You need an ORDER BY with your TOP 1 to select the correct row. You also don't need to repeat the expression three times.

您需要使用TOP 1的ORDER BY来选择正确的行。您也不需要重复三次表达式。

You could use

你可以用

INSERT INTO StockTransaction
            (TransactionType,
             TransactionId,
             TransactionItemId,
             OpeningQuantity,
             ClosingQuantity)
SELECT TransactionType,
       TransactionId,
       TransactionItemId,
       OpeningQuantity,
       CASE A.TransactionType
         WHEN 1
           THEN OpeningQuantity + A.Quantity
         WHEN 2
           THEN OpeningQuantity - A.Quantity
       END AS ClosingQuantity
FROM   @p_StockTransaction A
       CROSS APPLY (SELECT ISNULL((SELECT TOP 1 ClosingQuantity
                                   FROM   StockTransaction ST
                                   WHERE  ST.TransactionType = A.TransactionType
                                          AND ST.TransactionItemId = A.TransactionItemId
                                   ORDER  BY ST.TransactionId DESC /*??*/), 0)) CA(OpeningQuantity)

You should also consider concurrency. This will need additional locking hints if it may be called in parallel for the same TransactionType, TransactionItemId (and you will need to ensure there are no duplicates for this in the input TVP)

您还应该考虑并发性。如果可以为相同的TransactionType，TransactionItemId并行调用它，则需要额外的锁定提示（并且您需要确保输入TVP中没有重复项）

#1