Why is my SQL Server 2008 query working?

I have a query in SQL Server 2008 R2 like below, when I execute this query it keeps working ... how to debug to see what is going on with this code? any help. :)

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

      

+3


source to share


2 answers


Your loop depends on @runningstock <= 0 to complete. However, my tests show that @allocateqty ends up evaluating to 0! This means that "SET @runningstock = @runningstock - @allocateqty" stops the @runningstock decrement. At this moment, you are in an endless loop. Game over.

I used a very low tech method



    PRINT @runningstock
    PRINT @allocateqty

      

near the end of the loop so I can observe these values.

+5


source


It looks like you want to break the loop when @runningstock reaches 0 or less, judging by the combination of your "while" and "break" clauses. Try using "while (@runningstock> 0)" rather than the "> =" you are currently using.



0


source







All Articles