时间戳

首页 > 解决方案 > 更新临时表后，while循环未选择所需的记录

sql-server - 更新临时表后，while循环未选择所需的记录

问题描述

我有一个嵌套的 while 循环来从 #stock 表中选择一些股票记录。如果库存耗尽，我需要移动到下一个匹配的库存记录。我的循环不断从临时表中选择同一行。即使我删除了该行，并在嵌套的 Begin...End 中进行验证。

WHILE @DemandOutstandingQty > 0 --Check for stock
    BEGIN

SET ROWCOUNT 1
    SELECT @StockExpirationDate = ExpirationDate, @StockRemainingQty = RemainingQty, @StockLotNum = LotNum, @StockID = StockGUID
    FROM #stock WHERE PartNum = @DemandPartNum AND RemainingQty > 0
    ORDER BY ExpirationDate ASC

WHILE @StockID IS NOT NULL
BEGIN
SET ROWCOUNT 0

--several other if blocks here end with demand going to 0 and breaking to the outer While

IF (@StockExpirationDate > @DemandDueDate AND @StockRemainingQty < @DemandOutstandingQty) --Stock not enough to meet demand
    BEGIN
        UPDATE #Stock SET RemainingQty = 0 WHERE StockGUID = @StockID;
        --DELETE FROM #stock WHERE StockGUID = @StockID; --Tried this also
        SET @DemandOutstandingQty = @DemandOutstandingQty - @StockRemainingQty; 
        Print @@StockID --The stock record with no remaining stock or deleted
        Select * from #stock where StockGUID = @StockID; --returns no rows / updated remaining qty
    END;

SET ROWCOUNT 1
    SELECT @StockExpirationDate = ExpirationDate, @StockRemainingQty = RemainingQty, @StockLotNum = LotNum, @StockID = StockGUID
    FROM #stock 
    WHERE PartNum = @DemandPartNum AND RemainingQty > 0 AND StockGUID <> @StockID
    ORDER BY ExpirationDate ASC
    Print @StockID --Its the same ID, not moving to the next stock record

SET ROWCOUNT 0

END --@StockID while

--does some more stuff

END --Outer While

这可能是因为嵌套的 Begin...End 吗？

标签： sql-servertsql

解决方案

解决。

切换到 WHILE @StockRemainingQty > 0 作为条件，并在库存用完时设置 '@StockRemainingQty = 0。

感谢HABO的提醒。@stockID 没有改变。

推荐阅读