How to use WHILE LOOP to add value to list with condition, SQL Server 2008

I have a loop query with scenario below:

• orderqty = increase value 1 each loop
• runningstock = decrease value 1 each loop
• allocateqty = case when orderqty > 0 and runningstock > 0 then 1 else 0
• loop till runningstock=0 or total allocation=stockqty

Query:

DECLARE @RESULT TABLE (priority int,partcode nvarchar(50),orderqty int, runningstock 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',2),
    (2,'A',10),
    (3,'A',3),
    (4,'A',8);

INSERT INTO @STOCK(partcode,stockqty)
VALUES('A',20);

DECLARE @allocateqty int=1
DECLARE @runningstock int=(SELECT stockqty FROM @stock)

WHILE @runningstock>=0
BEGIN
    INSERT INTO @RESULT(priority,partcode,orderqty,runningstock,allocateqty)
    SELECT priority,
           partcode, 
           orderqty,
           @runningstock,
           CASE WHEN @runningstock > 0 AND orderqty > 0 THEN 1 ELSE 0 END
    FROM @order
    SET @runningstock -=1
 END

SELECT * FROM @Result

GO

Result:

priority    partcode    orderqty    runningstock    allocateqty
1   A   2   20  1
2   A   10  20  1
3   A   3   20  1
4   A   8   20  1
1   A   2   19  1
2   A   10  19  1
3   A   3   19  1
4   A   8   19  1
1   A   2   18  1
2   A   10  18  1
3   A   3   18  1
4   A   8   18  1
1   A   2   17  1
2   A   10  17  1
3   A   3   17  1
4   A   8   17  1
1   A   2   16  1
2   A   10  16  1
3   A   3   16  1
4   A   8   16  1
1   A   2   15  1
2   A   10  15  1
3   A   3   15  1
4   A   8   15  1
1   A   2   14  1
2   A   10  14  1
3   A   3   14  1
4   A   8   14  1
1   A   2   13  1
2   A   10  13  1
3   A   3   13  1
4   A   8   13  1
1   A   2   12  1
2   A   10  12  1
3   A   3   12  1
4   A   8   12  1
1   A   2   11  1
2   A   10  11  1
3   A   3   11  1
4   A   8   11  1
1   A   2   10  1
2   A   10  10  1
3   A   3   10  1
4   A   8   10  1
1   A   2   9   1
2   A   10  9   1
3   A   3   9   1
4   A   8   9   1
1   A   2   8   1
2   A   10  8   1
3   A   3   8   1
4   A   8   8   1
1   A   2   7   1
2   A   10  7   1
3   A   3   7   1
4   A   8   7   1
1   A   2   6   1
2   A   10  6   1
3   A   3   6   1
4   A   8   6   1
1   A   2   5   1
2   A   10  5   1
3   A   3   5   1
4   A   8   5   1
1   A   2   4   1
2   A   10  4   1
3   A   3   4   1
4   A   8   4   1
1   A   2   3   1
2   A   10  3   1
3   A   3   3   1
4   A   8   3   1
1   A   2   2   1
2   A   10  2   1
3   A   3   2   1
4   A   8   2   1
1   A   2   1   1
2   A   10  1   1
3   A   3   1   1
4   A   8   1   1
1   A   2   0   0
2   A   10  0   0
3   A   3   0   0
4   A   8   0   0

the correct one should be like this:

priority    partcode    orderqty    runningstock    allocateqty
1   A   2   20  1
2   A   10  19  1
3   A   3   18  1
4   A   8   17  1
1   A   1   16  1
2   A   9   15  1
3   A   2   14  1
4   A   7   13  1
1   A   0   12  0
2   A   8   12  1
3   A   1   11  1
4   A   6   10  1
1   A   0   9   0
2   A   7   9   1
3   A   0   8   0
4   A   5   8   1
1   A   0   7   0
2   A   6   7   1
3   A   0   6   0
4   A   4   6   1
1   A   0   5   0
2   A   5   5   1
3   A   0   4   0
4   A   3   4   1
1   A   0   3   0
2   A   4   3   1
3   A   0   2   0
4   A   2   2   1
1   A   0   1   0
2   A   3   1   1
3   A   0   0   0

Answer

You can also loop through your priorities and make sure the orderqty is updated accordingly. I’d do something like this:

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 1 ELSE 0 END

        INSERT INTO @RESULT(priority,partcode,orderqty,runningstock,allocateqty)
        SELECT @priority,
               partcode, 
               CASE WHEN @orderqty >= 0 THEN @orderqty ELSE 0 END AS orderqty,
               @runningstock,
               @allocateqty
        FROM @order 
        WHERE priority = @priority

        SET @priority += 1      
        SET @runningstock = @runningstock - @allocateqty
    END
    SET @allocatedqty += 1

    IF (@runningstock <= 0) BREAK 
 END

SELECT * FROM @Result

GO