--- In firebird-support@yahoogroups.com, Mark Deibert
<mark.deibert@g...> wrote:

> Here is a SP...
> ALTER PROCEDURE SP_LOAD (
> GAMEKEY BIGINT,
> PLAYERKEY BIGINT)
> RETURNS (
> PKEY INTEGER,
> DIEKEY INTEGER,
> STRIP VARCHAR(24))
> AS
> begin
> select PKEY,DIEKEY,STRIP from DICE
> where (GAMEKEY=:gamekey and PLAYERKEY=:playerkey)
> into pkey,diekey,strip;
> suspend;
> end
> I can enter: select PKEY,DIEKEY,STRIP from DICE where GAMEKEY=22

and

> PLAYERKEY=6
> ... and I get back several results row.
> If I enter: select PKEY,DIEKEY,STRIP from SP_LOAD(22,6)
> ... I get zero rows.
> This make no sense to me. Does it make any to you?

Yes it makes perfect sense

Two Problems:

1. MGA, understand how it works

Are they executed in the same transaction? (I bet not)

Remember that transaction2 can not see records committed by other
transactions after it (transaction2) has started. If they were
executed by the same transactions I would expect 1 record returned.

2. You are only going to get one record back at best. You need to use
the For .. do syntax to use the procedure like this.

ALTER PROCEDURE SP_LOAD (
GAMEKEY BIGINT,
PLAYERKEY BIGINT)
RETURNS (
PKEY INTEGER,
DIEKEY INTEGER,
STRIP VARCHAR(24))
AS
begin
for select PKEY,DIEKEY,STRIP from DICE
where (GAMEKEY=:gamekey and PLAYERKEY=:playerkey)
into pkey,diekey,strip
do
begin
suspend;
end
end

Adam