At 05:12 PM 22/09/2005 +0700, you wrote:

>In a stored procedure, a variable has been assigned a value.
>Later on, when it is used to receive select result which does not
>return any result, isn't the variable suppossed to be null now?

A variable is just a variable. You have to initialise it, you assign to
it, etc. etc.

>However, it is not. Please see a simple SP below. When the SP is
>executed, the expected result should ID1 has a value and ID2 should be
>NULL. However, the ID1 and ID2 both get the same value.

Correct. The second statement cannot return anything, so there is nothing
there to alter either the value of the variable (i.e. no value) or its
state (e.g. change it to null).

>Is it by designed? I know, the workaround is simple, but it makes us
>easily slipped forgetting to reset the variable to null before
>executing a select statement.

Do you make this excuse for yourself when you write code in other
programming languages? PSQL is no more designed for bad coding practice
than is any other language...

>CREATE PROCEDURE DOES_NOT_WANT_TO_BE_NULL
>RETURNS (
> ID1 INTEGER,
> ID2 INTEGER)
>AS
>declare variable ID_RESULT integer;
>BEGIN
> --ID_RESULT will get some value
> select RDB$RELATION_ID from RDB$DATABASE into ID_RESULT;
> ID1 = ID_RESULT;
>
> --Now ID_RESULT should be NULL, but it retains its old value
> select RDB$RELATION_ID from RDB$DATABASE
> where 'A dog talks' = 'Impossible' into ID_RESULT;
> ID2 = ID_RESULT;
> SUSPEND;
>END

It's worthwhile to use correct syntax when referring to variables
(including input and output arguments). The generic rule is that, if you
refer to a variable in an SQL statement, you prefix it with a colon.

Although it's the case that, over time, D. Yemanov has been silently
enabling the rule to be relaxed under some conditions, it is
undocumented; and, unless you are a real expert who really understands the
re-implementation, it would be best to stick with the documented rules to
ensure that you get predictable results.

./hb