RE: nested while statements / nested loops

Andreas Ackermann wrote

> Hello again,
> 
> in my opinion the problem is that a procedure is stoped the first time the END; 
> command occurs.
> See this little example with only one while-loop and a simple insert-statement following:
> 
> CREATE DBPROC TEST
> AS
> VAR var1 INTEGER; var2 CHAR(100)
> SELECT VAR1, VAR2 FROM TABLE1;
>     WHILE $rc = 0 DO BEGIN
>           FETCH INTO :var1, :var2;
>           INSERT INTO TABLE2 (a,b) Values (:var1, :var2);
>     END;
> INSERT INTO TABLE2 (a,b) Values (99, 'this insert statement will never happen!!!');
> 
> If you call the procedure the while loop will be correctly executed. 
> But you will not get a result from the second insert statement.
> Seems to me that the END; command terminates the hole procedure. 
> Therefore it is not possible to nest more than one loop, or to chain one after another.
> Please correct my if i'm wrong (and i hope so).
> 
> Regards
> Andreas
> 

It is exactly the other way round:
you will produce an endless loop only ending if your log/database is filled up.

Select --> rc = 0 --> start of while-loop
fetch --> rc = 0 --> insert --> rc = 0 --> fetch --> rc = 100 (noone cares for the rc of the fetch)
--> insert (with some values for :var1, :var2) --> rc = 0 --> fetch --> rc = 100 and so on
and so on and so.
You will not reach the second insert because of the not-checking of rc after fetch.

Elke
SAP Labs Berlin