Stored Procedre FOREACH cursor problem

Hi, I created a stored procedure with a FOREACH loop. The problem I' am having is that sometmes the SP will process every record in the cursor and sometimes it only process the first record and stops. I have 11 records in my test data set and it the same data set that this happens. The general syntax of my SP is as follows: FOREACH SELECT statement BEGIN perform calculations IF Condition a BEGIN call procedure 1a CONTINUE FOREACH END else IF Condition b BEGIN call procedure 2a END ELSE BEGIN call procedure 2b END END IF END IF END END FOREACH Appreciate the help. Roy

On Mon, 04 Dec 2000 03:21:13 GMT, "Roy de Silva" <cheeta@idirect.com> wrote: >Hi, I created a stored procedure with a FOREACH loop. The problem I' am >having is that sometmes the SP will process every record in the cursor and >sometimes it only process the first record and stops. I have 11 records in >my test data set and it the same data set that this happens. The general >syntax of my SP is as follows: > >FOREACH SELECT statement >BEGIN > perform calculations > IF Condition a > BEGIN > call procedure 1a > CONTINUE FOREACH > END > else > IF Condition b > BEGIN > call procedure 2a > END > ELSE > BEGIN > call procedure 2b > END > END IF > END IF >END >END FOREACH If any procedure You close the transaction with commit or rollback You close the cursor to, but: "The WITH HOLD keyword specifies that the cursor should remain open when a transaction closes (is committed or rolled back)." So use the statment FOREACH WITH HOLD SELECT ... Piotrek Wawrzyniak