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 to share