Hi Farshid,

> What is the problem with the following code?

Without examining it very closely, I see several problems:

> CREATE PROCEDURE SPTR

You don't specify any output parameters. But the SUSPEND later on suggests that you want to return something to the caller. So you need a RETURNS clause.

> AS
> DECLARE VARIABLE price INTEGER;
> DECLARE VARIABLE price1 INTEGER;
> DECLARE VARIABLE eid SMALLINT;
> DECLARE VARIABLE bsum INTEGER DEFAULT 0;
> DECLARE VARIABLE SCTN CURSOR FOR (
> Select ID,DBR,CDR
> From TBL_Transactions
> ORDER BY SDate);
> BEGIN
> OPEN SCTN;
> FETCH SCTN INTO eid, price,price1;
> WHILE (1=1) DO
> BEGIN
> bsum = bsum + (price-price1);
> update TBL_TRANSACTIONS set CBALANCE = bsum where Id = eId;
> FETCH SCTN INTO eid, price,price1;
> END

The WHILE loop will never end, because there is no LEAVE statement and 1=1 will remain true forever. You probably want something like "if (row_count = 0) then leave;" after the fetch. BTW, what if the first fetch (before loop entry) returns no data? Your code doesn't seem prepared for that.

Because of the endless loop, the code below will never be reached:

> CLOSE SCTN;
> --DEALLOCATE SCTN;
>
> SELECT ID,DBR,CDR FROM TBL_TRANSACTIONS ORDER BY SDate;

You have to select those values INTO local variables and/or output parameters.

> SUSPEND;

SUSPEND gives the caller the opportunity to fetch the current row of output parameters. But as said before, you haven't declared any.

Not sure if this is all that's wrong, but it's a start! ;-)


HTH,
Paul Vinkenoog