At 08:26 AM 31/01/2007, you wrote:

>Here is a stored procedure that is not working as I would like.
>This SP is supposed to update the record with a TickTime one minute
>before. But actually it will not update.

No, you are right. It will never update anything.

>CREATE PROCEDURE CALCSPEED (
> SYMB Varchar(10) )
>AS
>DECLARE VARIABLE Price0 DOUBLE PRECISION;
>DECLARE VARIABLE Price1 DOUBLE PRECISION;
>DECLARE VARIABLE Price2 DOUBLE PRECISION;
>DECLARE VARIABLE S DOUBLE PRECISION;
>DECLARE VARIABLE ONEMIN DOUBLE PRECISION;
>DECLARE VARIABLE TTime TIMESTAMP;
>BEGIN
> ONEMIN = CAST(1 as DOUBLE PRECISION)/CAST(24 as DOUBLE
>PRECISION)/CAST(60 as DOUBLE PRECISION);
> FOR SELECT Close,TickTime FROM MINUTEDATA Where Symbol=:Symb Order
>by TickTime
> INTO :Price2,:TTime
> DO
> BEGIN
> IF (Price0 IS NOT NULL) THEN

No value has been assigned to Price0. It will always be Null.

> BEGIN
> S = Price2-Price0;

Will always be null because Price0 is always null.

> UPDATE MINUTEDATA Set Speed=:S where
>TickTime=:TTime-:ONEMIN and Symbol=:Symb;
> END
> Price0 = Price1;

Would remain null because Price1 never gets a value.

> Price1 = Price2;

Both will be null, because the loop condition is never met and no
values are ever assigned to Price2 or TTime.

> END
>END^
>
>This is how I solved the problem but still I would like to understand
>why the first version would not work. I am using FB embedded 1.5

The second version has the same logical problems as the first. It
might run without exceptions, but it can never update anything - see
the inline note in your WHERE clause.

>CREATE PROCEDURE CALCSPEED (
> SYMB Varchar(10) )
>AS
>DECLARE VARIABLE Price0 DOUBLE PRECISION;
>DECLARE VARIABLE Price1 DOUBLE PRECISION;
>DECLARE VARIABLE Price2 DOUBLE PRECISION;
>DECLARE VARIABLE S DOUBLE PRECISION;
>DECLARE VARIABLE ONEMIN DOUBLE PRECISION;
>DECLARE VARIABLE TTime TIMESTAMP;
>DECLARE VARIABLE TTime0 TIMESTAMP;
>BEGIN
> ONEMIN = CAST(1 as DOUBLE PRECISION)/CAST(24 as DOUBLE
>PRECISION)/CAST(60 as DOUBLE PRECISION);
> FOR SELECT Close,TickTime FROM MINUTEDATA Where Symbol=:Symb Order
>by TickTime
> INTO :Price2,:TTime
> DO
> BEGIN
> IF (Price0 IS NOT NULL) THEN
> BEGIN
> S = Price2-Price0;
> UPDATE MINUTEDATA Set Speed=:S

>where TickTime=:TTime0

NULL = NULL evaluates to False, i.e. there will never be a match for
TTime0, so no records will be selected to update.

>and
>Symbol=:Symb;
> END
> Price0 = Price1;
> Price1 = Price2;
> TTime0 = TTime;
> END
>END^

Recommendations:

1. Initialise your variables at the start of the procedure and again
after each iteration of the FOR loop.

2. Study the Null Guide at the Documentation page of the Firebird
website to understand how null expressions are evaluated.

3. Make sure you explicitly test for NULL *everywhere*.

./heLen