At 07:25 PM 10/08/2007, you wrote:

>I have the following Firebird stored procedure:
>PROCEDURE FetchRoles(
> UserId CHAR(10))
>RETURNS
> RoleCode CHAR(8)
> RoleDesc VARCHAR(25))
>AS
>BEGIN
>FOR
> SELECT role_code, role_desc
> FROM user_roles UR, login_data LD
> WHERE UR.role_code = LD.user_role
> AND LD.user_id = :UserId
> AND LD.user_status = `Active'
> INTO :RoleCode, :RoleDesc
>DO
> SUSPEND;
>END^
>
>Each time I execute the procedure only the first record is returned,
>it is almost like the loop exits on the first match and does not
>return the whole dataset. The records in the two tables are:
>
>USER_ROLES (role_code, role_desc)
>CLNADMIN Client Administrator
>SPLADMIN Supplier Administrator
>STKADMIN Stock Administrator
>FINADMIN Financial Administrator
>
>LOGIN_DATA (user_id, user_role, user_status)
>TAANTON CLNADMIN Active
>TAANTON SPLADMIN Active
>TAANAOM SPLADMIN Active
>TAANAOM FINADMIN Active
>TAANTON FINADMIN Active
>
>In ISQL issuing the command EXECUTE PROCEDURE FetchRoles(`TAANTON')
>only the first record is returned CLNADMIN Client Administration and
>the other two records completely ignored. Is there something wrong
>within the procedure?

The procedure you have written is a SELECT procedure. Invoke it with
a SELECT statement:

SELECT * FROM FetchRoles (`TAANTON')

./heLen