Hello Daniel,

> > You're right Daniel, it is simple even though it can be made
> > more complicated and hide the simple solution by introducing
> > EXECUTE IMMEDIATE.
>
> I think the example I made for the original question was to simple, as I

am

> still getting errors.
>
> Sorry for the long post, but I think if I give you a copy of the actual
> procedure you may spot the error.
>
> SET TERM ^^ ;
> CREATE PROCEDURE P_LOGPAS_SEARCH (
> R_CREATED_BY VarChar(255),
> R_SHARED SmallInt)
> returns (
> R_ID Integer,
> R_CREATED_BY VarChar(255),

I think this is it - two parameters with the same name.

Perhaps you should rename the input parameter to:
IN_R_SHARED

As for myself, I usually name input parameter
IN_<something> and output parameters
OUT_<something>...

How else should Firebird know what parameter it
should use if you write R_CREATED_BY?

With regards,

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

> R_LOGIN VarChar(255),
> R_PASSWORD VarChar(255),
> R_URL VarChar(255),
> R_CREATED Date,
> R_MODIFIED Date,
> R_SHARED SmallInt,
> R_PRIMARY VarChar(32),
> R_SECONDARY VarChar(32),
> R_COMMENTS VarChar(1024),
> R_FILE_NAME VarChar(255))
> AS
> begin
> if( :R_SHARED = 0 )then
> begin
> SELECT R_ID,
> R_CREATED_BY,
> R_LOGIN,
> R_PASSWORD,
> R_URL,
> R_CREATED,
> R_MODIFIED,
> R_SHARED,
> R_PRIMARY,
> R_SECONDARY,
> R_COMMENTS,
> R_FILE_NAME
> FROM LOGPAS
> WHERE (( R_CREATED_BY = :R_CREATED_BY) AND ( R_SHARED =
> :R_SHARED))
> INTO
> :R_ID,
> :R_CREATED_BY,
> :R_LOGIN,
> :R_PASSWORD,
> :R_URL,
> :R_CREATED,
> :R_MODIFIED,
> :R_SHARED,
> :R_PRIMARY,
> :R_SECONDARY,
> :R_COMMENTS,
> :R_FILE_NAME;
> end
> else
> if( :R_SHARED = 1 )then
> begin
> SELECT R_ID,
> R_CREATED_BY,
> R_LOGIN,
> R_PASSWORD,
> R_URL,
> R_CREATED,
> R_MODIFIED,
> R_SHARED,
> R_PRIMARY,
> R_SECONDARY,
> R_COMMENTS,
> R_FILE_NAME
> FROM LOGPAS
> WHERE (( R_CREATED_BY = :R_CREATED_BY) OR ( R_SHARED =

:R_SHARED))

> INTO
> :R_ID,
> :R_CREATED_BY,
> :R_LOGIN,
> :R_PASSWORD,
> :R_URL,
> :R_CREATED,
> :R_MODIFIED,
> :R_SHARED,
> :R_PRIMARY,
> :R_SECONDARY,
> :R_COMMENTS,
> :R_FILE_NAME;
> end
> end ^^
>
> the error I get is:
>
> ISC ERROR CODE:335544351
>
> ISC ERROR MESSAGE:
> unsuccessful metadata update
> STORE RDB$PROCEDURE_PARAMETERS failed
> attempt to store duplicate value (visible to active transactions) in

unique

> index "RDB$INDEX_18"