Hi,

> 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?

no, of course not. If there's no resultset, why should it be set
to NULL?

> 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.
>
> 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.

A value will ONLY be assigned if there is a resultset, if not, no change.

> 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

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com