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



============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
www.techsol.org