Hi Christian,

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

If you don't initialize L_PRIV_ID - what value do you expect it to be?
When there are no rows for your SELECT ID ... INTO L_PRIV_ID -
what value do you want to put into L_PRIV_ID? There's no row, so
no value. Hence, the variable will keep holding the previous value.

(Re)Initializing your variables at the beginning of the loop is good
practice.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com