At 06:46 AM 4/09/2003 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
>
> >try committing the original query and then run your
> >altered SP again.
>
>step 1:
>create procedure test
>returns
>(result integer)
>as
>begin
> result=1;
> suspend;
>end
>
>step 2:
>create procedure test1
>returns
>(result integer)
>as
>begin
> select result from test into :result;
> suspend;
>end
>
>step 3:
>(try procedure test1)
>
>select result from test1
>
>Result=1 (as expected)
>
>step 4:
>alter procedure test
>returns
>(result integer)
>as
>begin
> result=2;
> suspend;
>end
>
>step 5:
>
>(try procedure test1)
>
>select result from test1
>
>Result=1 (instead of the expected value 2)
>
>step 6:
>
>(commit)
>
>commit
>
>step 7:
>
>(try procedure test1)
>
>select result from test1
>
>Result=1 (instead of the expected value 2)

Here is your problem:
step 3:
(try procedure test1)

select result from test1

Result=1 (as expected)

YOU NEED TO COMMIT THIS TRANSACTION.

step 4:
alter procedure test
returns
(result integer)
as
begin
result=2;
suspend;
end

IF YOU DON'T COMMIT THE TRANSACTION WITH THE SELECT, THE RECOMPILATION OF
THE SP IS DEFERRED.

Is that plain enough now?

> >if you are using a tool that performs CommitRetaining, the old
>version of the SP is still "engaged"
>
>Should I infer that this tool performs CommitRetaining ? (The tool is
>the old WISQL).

I don't know whether it does or not. It is an IB 5.6 tool, not a Firebird
tool.

>Is there a way to see the expected value 2 ?

Yes.

heLen