> I have a pretty simple query that works just fine when executed via
> IBExpert's SQL Editor, but when I put it into a stored procedure it fails
> and returns no results.
>
> This is in Firebird 1.5 Super Server on Linux.
>
> The stored procedure is:
>
> CREATE PROCEDURE SOUND_STORAGE_DETAILS_PRC (
> a_sound_no integer)
> returns (
> r_sound_name varchar(100) character set win1251,
> r_sound_desc varchar(5000) character set win1251,
> r_sound_server varchar(100) character set win1251,
> r_sound_volume varchar(100) character set win1251,
> r_sound_filename varchar(100) character set win1251,
> r_sound_duration time,
> r_sound_redirect varchar(250) character set win1251)
> as
> begin
> for
> select first 1 skip 0
> SOUND.SOUND_SHORT_NAME,
> SOUND.SOUND_DESC,
> SOUND_STORAGE.SS_SERVER,
> SOUND_STORAGE.SS_VOLUME,
> SOUND_STORAGE.SS_FILENAME,
> SOUND_STORAGE.SS_REDIRECT,
> SOUND.ITUNES_DURATION
> FROM
> SOUND
> INNER JOIN SOUND_STORAGE ON (SOUND.SOUND_NO = SOUND_STORAGE.SOUND_NO)
> WHERE
> (SOUND.SOUND_NO = :A_SOUND_NO)
> into :R_SOUND_NAME, :R_SOUND_DESC, :R_SOUND_SERVER, :R_SOUND_VOLUME,
> :R_SOUND_FILENAME, :r_sound_redirect, :R_SOUND_DURATION
> do
> begin
> suspend;
> end
> End
>
> ---
> What's really strange is that it works fine 99.9% of the time. But with
> certain data in the tables, it doesn't find it. But if I run the
> exact same
> query manually it works just fine.
>
> Does anyone have any idea as to why this could happen? All input
> and output
> arguments match exactly to the table specs and even if they didn't I would
> expect this to fail on all cases rather than just on some cases.
>
> Myles

I wonder why SKIP 0?
have you tried leaving this out? just use FIRST 1
and using the FIRST clause without an order by.. I never do it. Are you
looking for unreliable (unpredicable) results?

Alan