"Alan J Davies" <Aldis@...> wrote in message
news:200112190831_MC3-EB35-CB47@......

> Hi
> I am using RC1 with W2000 and have no major problems with stored

procedures

> and use them a lot. This particular one has caused a problem, however,

and

> will not compile after closing and restarting the server.

And it's because you have repeated names:

> attempt to store duplicate value (visible to active transactions) in

unique

> index "RDB$INDEX_18"

SQL> show index rdb$index_18;
RDB$INDEX_18 UNIQUE INDEX ON RDB$PROCEDURE_PARAMETERS(RDB$PROCEDURE_NAME,
RDB$PARAMETER_NAME)
SQL>

This is saying that a combination of proc and param name is not unique, see
below where I marked your code with asterisks:

> CREATE PROCEDURE SP_EventLog
> (
> Tag_No CHAR(20), **********************
> EventType smallint
> )
> RETURNS
> (
> HI_LO CHAR(10),
> EVENT_DATE TIMESTAMP,
> EVENT_TIME CHAR(8),
> TAG_NO CHAR(20), *******************
> TAG_NAME CHAR(30),

You decide which one to change. You could make the first one case-sensitive
by writing
"Tag_No" CHAR(20),

but I consider case-sensitive identifiers a masochist option. Rename one of
your parameters.
Perhaps we could extend the index to be unique among proc name, param name
and param type (input/output), but all the stored procedure parsing code
would have to be revised. I don't think it's worth the effort.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing