While Loop SAP B1 SQL stored procedure for blocking

Question

I have an issue with my stored procedure SAP B1.

What I'm trying to do here is storing the sum of the quantity, group by the manufacturing order and insert it into the temp table. Then use a while loop to go thru each ID to compare with the user table [@FGTRACKING] and block if temp.quantity > sum(quantity) in [@FGTracking].

However this is not working, the transaction still passed the stored procedure block. I suspect there is something wrong with my syntax.

IF @transaction_type IN ('A') AND @object_type = '67'
    BEGIN
    declare @top as int
    declare @temp table (id int,quantity int NOT NULL, monum int NOT NULL)
    insert into @temp (id, quantity,monum)
    select row_number() over (order by (select NULL)), sum(quantity) as quantity, u_shipment_line as monum
    from wtr1 t1
    where t1.docentry = @list_of_cols_val_tab_del
    group by u_shipment_line
    set @top = 1
    WHILE @top <= (select count(monum) from @temp)
    BEGIN
    IF EXISTS (select t100.monum from @temp t100 
    where t100.quantity > (select sum(t111.u_transfer) 
    from [@FGTRACKING] t111 where t111.u_mo_num = t100.monum 
    group by t111.u_mo_num) and t100.id = @top)
    BEGIN
    SELECT @Error = 666, @error_message = 'Over-transfer'
    END
    ELSE
    set @top = @top + 1
    END
    END

Edit your question and use proper indentation. You will immediately see what your error is. — FDavidov, Nov 29 '16 at 10:08

score 0 · Accepted Answer · answered Nov 29 '16 at 18:35

It looks like you're only incrementing your iterator (@top) when you don't encounter your error condition, so if your error condition triggers, you're stuck in an infinite loop.

Get rid of your "else" and always increment @top, or alternatively break out of your while loop when you hit your error condition.

...
ELSE  -- Get rid of this else
set @top = @top + 1
...

While Loop SAP B1 SQL stored procedure for blocking

1 Answers1