Hi,

> 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),
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"

Thank you

Daniel



____________________________
Comvision Pty. Ltd.

www.comvision.net.au