At 01:28 PM 30/06/2004 +0200, you wrote:

>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

should be
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;

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

It's just a variable. A variable just holds its current value. So, the
result at the end of the iteration will change only if a row was found, or
will remain at its old value if no row is found. "No rows found" doesn't
cause the variable somehow to change to null!!

So your strategy of initialising it to null at the beginning of the loop is
necessary for the way you have chosen to perform the existence check.

A much more robust way to do this check is not to rely on a variable but to
use a proper SQL existence check, like this:

[...]
FOR SELECT ID FROM T_Privilege_Type INTO :L_TYP_ID DO
BEGIN
if (exists (
SELECT ID
FROM T_Privileges
WHERE Z_PrivilegeType = :L_Typ_ID AND User_ID = :I_User_ID))
then
INSERT INTO T_Privileges (Z_PrivilegeType, Is_Privileged, User_ID)
VALUES (:L_Typ_ID, 'N', :I_User_ID);
END
[...]

/heLen