CODEWITHSUNDEEP
sqlsql-server
Sokea
Sokea

Reputation: 325

Why my SQL Server 2008 query keep running?

I have a query in SQL Server 2008 R2 as below, when i execute this query, it keeps running...how to debug to find out what is going wrong with this code? any help idea pls. :)

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

Upvotes: 3

Views: 627

Answers (2)

LeeCambl
LeeCambl

Reputation: 388

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

Upvotes: 0

JohnS
JohnS

Reputation: 2052

Your loop depends on @runningstock <= 0 to terminate. Yet my tests shows that @allocateqty eventually evaluates to 0! That means that "SET @runningstock = @runningstock - @allocateqty" stops decrementing @runningstock. At that point you are in an infinite loop. Game over.

I used the very low tech method of

    PRINT @runningstock
    PRINT @allocateqty

near the end of the loop so I could watch those values.

Upvotes: 5

Related Questions