Subject Re: [firebird-support] SELECT FIRST 1 .... problem
Author Helen Borrie
At 01:27 PM 30/10/2005 +0530, you wrote:
> The following SQL Statement returns NULL from the sql prompt
>and returns 0 inside the Stored Procedure when the WHERE
>condition is not met. Is it an expected behaviour ?
> SELECT FIRST 1 PD.product_id
> FROM purchase_det PD
> WHERE ( PD.company_id = :dCID ) AND ( PD.isbn = :sString )
> INTO :liPrID;

You have two behaviours here and both are expected.

In the first case, in ISQL, you won't get anything but exceptions, because
ISQL won't recognise this syntax. However, if you mean that a fully
populated, valid SQL query returned a null product_id then that would be
correct behaviour if there were no matches found for the WHERE criteria.

In the second case, if the query returns no matches, then your variable
IiPrID will simply retain the value it started with, presumably 0.