sql - 为什么我的 SQL Server 2008 查询一直在运行？

Question

我在 SQL Server 2008 R2 中有一个查询，如下所示，当我执行这个查询时，它一直在运行......如何调试以找出这段代码出了什么问题？任何帮助想法请。 :)

DECLARE @RESULT TABLE (
     priority int,
     partcode nvarchar(50),
     orderqty int, 
     allocateqty int) 
DECLARE @ORDER TABLE(
     priority int,
     partcode nvarchar(50),
     orderqty int) 
DECLARE @STOCK TABLE(
     partcode nvarchar(50),
     stockqty int) 

INSERT INTO @ORDER (priority,partcode,orderqty) 
VALUES(1,'A',10),     
      (2,'A',40); 
INSERT INTO @STOCK(partcode,stockqty) 
VALUES('A',22);

IF (SELECT SUM(orderqty)FROM @ORDER)<(SELECT stockqty FROM @STOCK)
BEGIN
 INSERT INTO @RESULT(priority,partcode,orderqty,allocateqty)
 SELECT priority, partcode,orderqty,orderqty
 FROM @ORDER
END
ELSE
BEGIN
DECLARE @allocatedqty int = 0
DECLARE @allocateqty int = 1
DECLARE @runningstock int = (SELECT stockqty FROM @stock)
WHILE @runningstock>=0
BEGIN
    DECLARE @priority int
    SELECT TOP 1 @priority = priority FROM @order ORDER BY priority ASC
    WHILE @priority <= (SELECT MAX(priority) FROM @order)
    BEGIN
        DECLARE @orderqty int
        SELECT @orderqty = orderqty - @allocatedqty FROM @order WHERE priority = @priority
        SELECT @allocateqty = CASE WHEN @runningstock > 0 AND @orderqty > 0 THEN @allocateqty ELSE 0 END
        INSERT INTO @RESULT(priority,partcode,orderqty,allocateqty)
        SELECT @priority,
               partcode, 
               CASE WHEN @orderqty >= 0 THEN @orderqty ELSE 0 END AS orderqty,
               @allocateqty
        FROM @order 
        WHERE priority = @priority
        SET @priority += 1      
        SET @runningstock = @runningstock - @allocateqty
    END
    SET @allocatedqty += @allocateqty
    IF (@runningstock <= 0) BREAK 
 END
END;
SELECT priority,partcode,SUM(allocateqty) AS [allocateqty]
FROM @Result
GROUP BY priority,partcode

Answer 1

您的循环取决于@runningstock <= 0 来终止。然而我的测试表明 @allocateqty 最终计算为 0!这意味着“SET @runningstock = @runningstock - @allocateqty”停止递减@runningstock。那时你处于无限循环中。游戏结束。

我使用了非常低科技的方法

    PRINT @runningstock
    PRINT @allocateqty

接近循环结束时，我可以看到这些值。