I've had people in here tell me that using a stored procedure that selects
records without a "suspend" command at the end will not return records or is
incorrect syntax. I'm not saying you're all wrong but I just came across an
example in my application where this is obviously proving that axiom to be
incorrect.

This stored procedure returns records consistently and has never caused a
problem:

create or alter procedure sel_prod_opt_by_prod_id
(
product_id int
)
returns
(
product_option_id_ int,
product_option_ varchar(50),
description_ varchar(500),
price_ decimal(9, 2)
)
AS
begin
select
po.product_option_id,
--po.product_id,
po.product_option,
po.description,
po.price
from
product_option po
inner join
product_to_product_option p2po
on
p2po.product_option_id = po.product_option_id
left join
product p
on
p.product_id = p2po.product_id
where
p.product_id = :product_id
into
:product_option_id_,
:product_option_,
:description_,
:price_;
end
^


I call it like so ("x" being my param value):

execute procedure sel_prod_opt_by_prod_id(x);

If I try to put suspend at the end of that query it won't compile. I've
tried it like this:

.......
into
:product_option_id_,
:product_option_,
:description_,
:price_;
suspend;
end
^

...and like this:

.......
into
:product_option_id_,
:product_option_,
:description_,
:price_
suspend;
end
^

Must it be in a loop to use "suspend"? There is no point in looping this,
it is only allowed to return one record. And besides, this works w/o fail.
My application is a web app written in C#/asp.net using the .NET Firebird
provider (1.6.3). For you .NET-ers out there, I'm using an FbCommand +
DataView to get the data out, perhaps there's an exception to the rule
there?

What's the story w/ that?

Thanks!

-v



[Non-text portions of this message have been removed]