CODEWITHSUNDEEP
sqlsql-server
A_Sk
A_Sk

Reputation: 4630

Insert Value Based On Previous Rows When Using Table Parameter For Insert Statement

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?

Upvotes: 1

Views: 57

Answers (1)

Martin Smith
Martin Smith

Reputation: 452957

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.

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)

Upvotes: 3

Related Questions