Christian,



Christian Gütter schrieb:

> Hi,
>
> I have got a little problem with the following code in
> one of my stored procedures:
>

> [...]
> FOR SELECT ID FROM T_Privilege_Type INTO L_TYP_ID DO
> BEGIN
> /* L_Priv_ID = NULL; */
> SELECT ID
> FROM T_Privileges
> WHERE ((Z_PrivilegeType = :L_Typ_ID) AND (User_ID = :I_User_ID))
> INTO L_Priv_ID;

this should read
INTO :L_Priv_ID;
^

> IF (L_Priv_ID IS NULL) THEN
> INSERT INTO T_Privileges (Z_PrivilegeType, Is_Privileged, User_ID)
> VALUES (:L_Typ_ID, 'N', :I_User_ID);
> END
> [...]
>
> As you can see, the code checks wether a record for a certain
> privileges(selected at the beginning of the loop) for a user exists.
> If it does not exist, this record is created.
>
> My problem is that this code only works when the "L_Priv_ID = NULL"
> (after the first "BEGIN") is _not_ commented out. Otherwise, it does
> not work. It seems that L_Priv_ID keeps the value of the last
> iteration when the result of the current iteration should be NULL.
>
> Is this a bug or a feature?

Neither nor.

If you select from a table that will not return any record then the
variable is NOT NULL since it won't be touched!

You get NULL if you select a field from a record that hasn't got a
value. But if you don't select any record at all you get nothing. And
nothing is NOT equivalent to NULL!

It's a good idea to initialize the return variable within a loop to be
sure you're not using an old value / state.

Luc.